理解复杂SQL语句的最佳方法是什么？

Question

有没有方法能够理解复杂的SQL语句？在阅读结构化/面向对象的代码时，通常有多层抽象帮助你将其分解成可管理的部分。然而，在SQL中，似乎你必须同时跟踪查询的多个部分。

提出这个问题的动机是关于复杂连接的SQL查询的 这个问题。在盯着回答中的查询语句数分钟后，我最终决定使用特定记录逐步执行查询，以了解每个部分正在发生什么。那是我能想到的唯一理解查询的方法。

有没有更好的方法将SQL查询分解成可管理的部分？

- Eric Ness

---

我要去谷歌一下Jon Skeet，虽然我承认这个名字听起来很熟悉。 - Sizons

Answer 1

当我看到一段复杂的SQL代码时，我会做以下事情：

首先，如果是更新或删除操作，并且没有被注释掉的话，我会添加代码将其转为选择操作。第一次尝试更新或删除操作之前，一定要先进行选择操作并查看结果。如果是更新操作，我会确保所选内容显示当前值以及我将设置的目标值，以确保我得到预期的结果。

理解连接非常关键，尤其是在理解复杂SQL的过程中。对于每个连接，我都会问自己它为什么存在？有四个基本原因：你需要一个列来选择、你需要一个字段用于where子句、你需要连接作为第三个表的桥梁，或者你需要连接到表以过滤记录（例如检索具有订单但不需要订单详情的客户的详细信息，这通常可以通过IF EXISTS where子句更好地完成）。如果是左连接或右连接（我倾向于重写所有内容使其成为左连接，这样生活会更简单），我会考虑是否可以使用内连接。为什么我需要左连接？如果我不知道答案，我会运行两种方式并查看数据之间的差异。如果有派生表，我会首先查看它们（只运行选择的部分以了解结果），以了解它们存在的原因。如果有子查询，我会尝试理解它们，并且如果它们很慢，我会尝试将其转换为派生表，因为这些通常更快。

接下来，我会查看where 子句。这是你在特定数据库中建立扎实基础的地方之一。例如，我知道在我的数据库中，有哪些场合可能只需要查看邮寄地址，有哪些场合可能需要查看其他地址。这可以帮助我知道where子句中是否遗漏了某些内容。否则，我会考虑where子句中的每一项，并弄清楚为什么它需要在那里，然后我会考虑是否有任何遗漏的内容应该在那里。在检查完毕后，我会考虑是否可以进行调整以使查询可搜索。

我首先会关注查询结果列表中的复杂部分。那个case语句是干什么用的？为什么要使用子查询？那些函数是做什么用的？（我总是查阅我不熟悉的函数代码。）为什么要使用distinct？是否可以通过使用派生表或聚合函数和group by语句来消除它？

最后而且最重要的是，基于我对业务的了解，我运行查询，并确定结果是否正确。如果你不了解你的业务，你就不知道查询是否正确。语法上的正确并不意味着结果正确。通常，你现有的用户界面的某一部分可以作为指导，以判断结果是否正确。例如，如果我有一个屏幕显示客户订单，我正在做一个包括客户订单的报告，我可能会抽查几个客户，以确保它显示了正确的结果。

如果当前的查询过滤不正确，我会删除其中的一些部分，以找出是什么导致了我不想要的记录被去掉，或者添加了我不想要的记录。通常情况下，你会发现连接方式是一对多，而你需要的是一对一（在这种情况下使用派生表！），或者你会发现你认为你需要在where子句中的某些信息并不适用于你需要的所有数据，或者where子句中缺少某些信息。在执行此操作时，在where子句中（如果它们不在select语句中）包含所有字段会有所帮助。甚至显示所有连接表的所有字段，仔细查看数据也可能会有所帮助。当我这样做时，我经常在where子句中添加一个小条件来抓取一些记录，而不是所有记录。

一个会破坏很多查询的难缠问题是where子句引用左连接右侧表中的字段。这将把它转换为内连接。如果你真的需要左连接，你应该把这些条件添加到连接本身中。

- HLGEM

---

+1 - 涵盖了我在这个问题中要提到的关键点。 - ConcernedOfTunbridgeWells

Answer 2

以下是一些有用的提示：

• 注释 - 理解代码块的作用并对其进行注释，以便在以后查看时能够理解它。
• 语法高亮 - 确保使用具有颜色编码功能的工具查看代码。
• 缩进 - 重新组织查询，使其更符合你的逻辑。可以使用制表符将内容向右缩进，添加换行符等。

例如：

select ID, Description, Status from ABC where Status = 1 OR Status = 3

可以更好地改写为：

select 
  ID,
  Description,
  Status
from ABC
where
  Status = 1 OR
  Status = 3

如果使用更复杂的查询，你会看到更大的优势。

- fallen888

Answer 3

以下是遵循的步骤来解决查询问题。

1. 首先，我会整理SQL的格式。
2. 然后，我会将SQL的所有部分以外的注释掉，只留下最基本或者最重要的表的基础部分来回答问题。
3. 然后，我会开始取消注释操作连接、选择列、分组、排序字段和过滤器来隔离查询的不同部分，以了解发生了什么。或者使用某些工具中的高亮执行方法。
4. 子查询通常可以独立执行。

执行每个步骤通常能让我更好地了解查询中发生了什么。

- John MacIntyre

---

@le dorfier-感谢您重新格式化...现在阅读起来更容易了。 - John MacIntyre

这是最好的可用答案，也描述了我的方法。 - dkretz

Answer 4

缩进和注释可以帮助很多。 我遇到的最有价值的东西是WITH语句。它在Oracle中，用于子查询重构。它允许您将一个大查询分解成一组看似较小的查询。每个查询都更易于管理。

这里是一个示例

WITH 
ssnInfo AS
(
    SELECT                   SSN, 
           UPPER(LAST_NAME)  LAST_NAME, 
           UPPER(FIRST_NAME) FIRST_NAME, 
                             TAXABLE_INCOME,          
                             CHARITABLE_DONATIONS
    FROM IRS_MASTER_FILE
    WHERE STATE = 'MN'                 AND -- limit to in-state
          TAXABLE_INCOME > 250000      AND -- is rich 
          CHARITABLE_DONATIONS > 5000      -- might donate too
          
),
doltishApplicants AS
(
    SELECT SSN, 
           SAT_SCORE,
           SUBMISSION_DATE
    FROM COLLEGE_ADMISSIONS
    WHERE SAT_SCORE < 100          -- About as smart as a Moose.
),
todaysAdmissions AS
(
    SELECT doltishApplicants.SSN, 
           TRUNC(SUBMISSION_DATE)  SUBMIT_DATE, 
                                   FIRST_NAME
    FROM ssnInfo,
         doltishApplicants
    WHERE ssnInfo.SSN = doltishApplicants.SSN

)
SELECT 'Dear ' || FIRST_NAME || 
       ' your admission to WhatsaMattaU has been accepted.'
FROM todaysAdmissions
WHERE SUBMIT_DATE = TRUNC(SYSDATE)    -- For stuff received today only
;

使用内联视图也可以实现相同的功能，但“with”还能在需要时创建临时表。在某些情况下，您可以将子查询复制并在大查询上下文之外执行它。

此形式还允许您将过滤子句与各个子查询一起放置，并将连接子句保存到最终选择中。

在我们的工作中，开发团队通常发现这种方法更易于维护，而且通常更快。

- EvilTeach

---

WITH子句也被SQL Server 2005和SQL Server 2008所支持。 - Registered User

Answer 5

大多数情况下，这只需要经验和适当的缩进。

- Stephane Grenier

Answer 6

格式化有所帮助，但理解集合论以及关系理论更有帮助。

对查询执行方式的模糊了解也不会有害（表扫描、索引扫描、索引跳跃、哈希表合并等）；查询计划器可以显示这些操作。

一些操作（如having、exists、with）起初可能会有些棘手。

首先要理解每个表发生了什么，以及表是如何连接的。

- Steven A. Lowe

---

我刚刚基于旧视图模仿了一个新视图，并且使用了我自己编写的“OVER（PARTITION BY”语句，它可以正常工作，但我不知道它为什么能够正常工作。 - Paul Tomblin

是的，我也使用CTE和PIVOT进行相同的操作。总有一天我会搞清楚它们的用法。 - Jimmy

没有什么比那些专有语言扩展更能使事情变得晦涩。还有那些使用它们来证明自己能力的程序员。 - dkretz

这些都是 SQL 标准的一部分，而不是专有扩展。 - anon

Answer 7

我发现回到逻辑查询处理阶段，并使用样本数据逐步分解查询通常是有帮助的。

(以下内容摘自Itzik Ben-Gan的《Microsoft SQL Server 2005内部：T-SQL查询》。)

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

1. FROM: 在FROM子句中的前两个表之间执行笛卡尔积（交叉连接），从而生成虚拟表VT1。
2. ON: 应用ON筛选器到VT1。只有满足条件的行才会被插入到VT2中。
3. OUTER (join): 如果指定了OUTER JOIN（而不是CROSS JOIN或INNER JOIN），则来自保留表或未找到匹配项的表的行将作为外部行添加到VT2的行中，生成VT3。如果在FROM子句中出现了两个以上的表，则在最后一个连接的结果和FROM子句中的下一个表之间重复执行步骤1至3，直到处理完所有表。
4. WHERE: 应用WHERE筛选器到VT3。只有满足条件的行才会被插入到VT4中。
5. GROUP BY: 根据GROUP BY子句中指定的列列表对VT4中的行进行分组。生成VT5。
6. CUBE | ROLLUP: 将超级组（组合的组）添加到VT5的行中，生成VT6。
7. HAVING: 应用HAVING筛选器到VT6。只有满足条件的组才会被插入到VT7中。
8. SELECT: 处理SELECT列表，生成VT8。
9. DISTINCT: 从VT8中删除重复的行。生成VT9。
10. ORDER BY: 根据ORDER BY子句中指定的列列表对VT9中的行进行排序。生成一个游标（VC10）。
11. TOP: 从VC10的开头选择指定数量或百分比的行。生成表VT11并返回给调用者。

- fractor

Answer 8

另一个重要的是使用标准的连接语法：

SELECT A 
  FROM B
  JOIN C ON B.ID = C.ID
 WHERE C.X = 1

不是

SELECT A 
  FROM B
     , C 
 WHERE B.ID = C.ID 
   AND C.X = 1

- FerranB

Answer 9

我想这完全取决于经验。我觉得那些问题中的查询并不是很复杂，可能是因为我经常运行的查询比那些更复杂。

合适的编码标准当然有助于理解查询，因为它可以将查询分成视觉上较小和格式更好的块。当涉及子查询时，最好首先了解它们的功能，并在查看完整的查询时使用该理解。

- Eran Galperin

Answer 10

与任何事情一样，最好的方法是自己写很多复杂的SQL语句。最终，一般的结构方式会变得明显起来。当然，如果你想要快速的话，这可能不是正确的方式。

空格非常重要。一个看起来非常复杂的查询，当正确的空格存在时，可以看起来几乎简单。

至于连接（joins）...嗯，对不起，我在这里无法提供非常有帮助的信息，因为我的答案是了解通用连接的工作原理是了解特定连接的最佳方式。每种类型的连接都有非常具体的目的，如果您知道它们的工作原理，从将x连接到y，将x连接到y再连接到z，或者将x和y连接到a和b之间应该没有太大区别。

然而，更有帮助的是，您需要立即查看最内部的部分。与您可能习惯于从代码的宏观角度查看然后深入细节不同，使用查询会更加有帮助和容易理解，如果您从细节开始并逐步向外推进。

从任何子查询开始，弄清它们以单个查询的方式执行的内容（如果可能），然后逐步向外移动，直到达到顶部。再次提到连接（joins）...真的只需要找到解释连接的网页，并进行一些测试，直到完全理解为止。并没有什么简单方式，因为一旦您理解了它们，您几乎可以找出您想要的任何连接。

- Gene Roberts