SQL 语句缩进的好习惯

Question

提问by MagicAndi

What is the accepted practice for indenting SQL statements? For example, consider the following SQL statement:

缩进 SQL 语句的公认做法是什么？例如，考虑以下 SQL 语句：

SELECT column1, column2
FROM table1
WHERE column3 IN
(
SELECT TOP(1) column4
FROM table2
INNER JOIN table3
ON table2.column1 = table3.column1
)

How should this be indented? Many thanks.

这应该如何缩进？非常感谢。

Answer 1

采纳答案by Patrick Desjardins

SELECT column1
     , column2
FROM table1
WHERE column3 IN
(
    SELECT TOP(1) column4
    FROM table2
    INNER JOIN table3
    ON table2.column1 = table3.column1
)

I like to have all "," in front, this way I never search them when an error at line X from the SQL editor.

我喜欢把所有的“,”放在前面，这样我就不会在 SQL 编辑器的 X 行出现错误时搜索它们。

This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

对于不使用这种类型的编写 SQL 语句的人来说，这是一个示例。两者都包含缺少逗号的错误。

SELECT sdcolumn123
 , dscolumn234
 , sdcolumn343
 , ffcolumn434
 , sdcolumn543
 , bvcolumn645
  vccolumn754
 , cccolumn834
 , vvcolumn954
 , cvcolumn104
FROM table1
WHERE column3 IN
(
    ...
)

SELECT sdcolumn123, dscolumn234, asdcolumn345, dscolumn456, ascolumn554, gfcolumn645 sdcolumn754, fdcolumn845, sdcolumn954, fdcolumn1054
FROM table1
WHERE column3 IN
(
    ...
)

I found easier and more quick at the first example. Hope this example show you more my point of view.

我在第一个例子中发现更容易和更快。希望这个例子能告诉你更多我的观点。

Answer 2

回答by Bill the Lizard

SELECT column1, column2
FROM table
WHERE column3 IN (
    SELECT TOP(1) column4
    FROM table2
    INNER JOIN table3 ON table2.column1 = table3.column1
)

This is pretty short and easy to read. I'd make adjustments if there were more columns selected or more join conditions.

这很短而且很容易阅读。如果选择了更多列或更多连接条件，我会进行调整。

Answer 3

回答by Codewerks

Not sure there is an accepted practice, but here's now how I'd do it:

不确定是否有公认的做法，但现在我会这样做：

SELECT 
    column1, 
    column2 
FROM 
    table1 
WHERE 
    column3 IN 
    ( 
     SELECT TOP(1) 
         column4 
     FROM 
         table2 
         INNER JOIN 
         table3 
             ON table2.column1 = table3.column1 
    )

Answer 4

回答by jalbert

I like to have "rivers" of white space in the code. It makes it a little easier to scan.

我喜欢在代码中有空白的“河流”。它使扫描更容易一些。

SELECT column1,
       column2
  FROM table1
 WHERE column3 IN (SELECT column4
                     FROM table2
                     JOIN table3
                       ON table2.column1 = table3.column1);

Answer 5

回答by Slapout

I like jalbert's form of lining up the keywords on their right. I'd also add that I like the ANDs and ORs on the left (some people put them on the right.) In addition, I like to line up my equals signs when possible.

我喜欢 jalbert 将关键字排列在右边的形式。我还要补充一点，我喜欢左边的 AND 和 OR（有些人把它们放在右边。）此外，我喜欢尽可能排列我的等号。


SELECT column1, 
       column2  
  FROM table1, table2 
 WHERE table1.column1 = table2.column4 
   AND table1.col5    = "hi" 
    OR table2.myfield = 678

Answer 6

回答by LeppyR64

This is my personal method. Depending on the length of the join condition I sometimes indent it on the line below.

这是我个人的方法。根据连接条件的长度，我有时会在下面的行中缩进它。

SELECT
  column1,
  column2
FROM
  table1
WHERE
  column3 IN ( 
    SELECT TOP(1)
      column4
    FROM
      table2
      INNER JOIN table3 ON table2.column1 = table3.column1
  )


SELECT
  column1,
  column2
FROM
  table1
WHERE
  column3 IN ( 
    SELECT TOP(1)
      column4
    FROM
      table2
      INNER JOIN table3
        ON table2.column1 = table3.column1 -- for long ones
  )

Answer 7

回答by Mike Burton

I've written a code standard for our shop that is biased in the extreme towards readability/"discoverability" (the latter being primarily useful in insert-select statements):

我为我们的商店编写了一个代码标准，它极端偏向于可读性/“可发现性”（后者主要用于插入选择语句）：

SELECT 
    column1, 
    column2
FROM 
    table1
WHERE 
    column3 IN
    (
        SELECT TOP(1) 
            column4
        FROM 
            table2
            INNER JOIN table3 ON table2.column1 = table3.column1
    )

On more complex queries it becomes more obvious how this is useful:

在更复杂的查询中，它的用处变得更加明显：

SELECT
    Column1,
    Column2,
    Function1
    (
        Column1,
        Column2
    ) as Function1,
    CASE
    WHEN Column1 = 1 THEN
        a
    ELSE
        B
    END as Case1       
FROM
    Table1 t1
    INNER JOIN Table2 t2 ON t1.column12 = t2.column21
WHERE
    (
        FilterClause1
        AND FilterClause2
    )
    OR
    (
        FilterClause3
        AND FilterClause4
    )

Once you move to systems with more than a single join in most of your queries, it has been my experience that using vertical space liberally is your best friend with complex SQL.

一旦您迁移到在大多数查询中具有多个连接的系统，根据我的经验，自由使用垂直空间是处理复杂 SQL 的最佳朋友。

Answer 8

回答by DOK

If you have a lengthy SQL statement that you'd like to reformat without all the typing and tabbing, you can slap it into this websiteand get a nicely formatted result. You can experiment with various formats to see which makes your text the most readable.

如果你有一个冗长的 SQL 语句，你想重新格式化而不用所有的输入和制表符，你可以把它放到这个网站上，并得到一个很好的格式化结果。您可以尝试各种格式，以查看哪种格式使您的文本最具可读性。

Edit: I believe that thisis the 2014 location of the SQL formatter.

编辑：我相信这是 SQL 格式化程序的 2014 位置。

Answer 9

回答by Charles Bretana

SQL formatting is an area where there is a great deal of variance and disagreement... But fwiw, I like to focus on readability and think that whatever you do, consistently conforming to any rules that reduce readability is, as the old cliche goes, a "foolish consistency" ( "Foolish consistency is a hobgoblin for simple minds" )

SQL 格式是一个存在大量差异和分歧的领域......但是，我喜欢专注于可读性，并认为无论你做什么，始终遵守任何降低可读性的规则，就像老生常谈的那样， “愚蠢的一致性”（“愚蠢的一致性是简单头脑的妖精”）

So, instead of calling them rules, here are some guidelines. For each Major clause in a SQL statement (Select, Insert, Delete, From, Where, Having, Group BY, Order By, ... I may be missing a few) should be EASILY identifiable. So I generally indent them at the highest level, all even with each other. Then within each clause, I indent the next logical sub structure evenly... and so on.. But I feel free to (and often do) change the pattern if in any individual case it would be more readable to do so... Complex Case statements are a good example. Because anything that requires horizontal scrolling reduces readability enormously, I often write complex (nested) Case expressions on multiple lines. When I do, I try to keep the beginning of such a statement hanging indent based on it's logical place in the SQL statement, and indent the rest of the statement lines a few characters furthur...

因此，这里不是将它们称为规则，而是一些指导方针。对于 SQL 语句中的每个 Major 子句（Select、Insert、Delete、From、Where、Having、Group BY、Order By……我可能会遗漏一些）应该很容易识别。所以我通常在最高级别缩进它们，甚至彼此之间。然后在每个子句中，我均匀地缩进下一个逻辑子结构......等等......但是我可以随意（并且经常这样做）更改模式，如果在任何个别情况下这样做会更具可读性...... Complex Case 语句就是一个很好的例子。因为任何需要水平滚动的东西都会极大地降低可读性，所以我经常在多行上编写复杂的（嵌套的）Case 表达式。当我这样做时，我尝试根据它在 SQL 语句中的逻辑位置来保持这样一个语句的开头悬挂缩进，

SQL Database code has been around for a long time, since before computers had lower case, so there is a historical preference for upper casing keywords, but I prefer readability over tradition... (and every tool I use color codes the key words now anyway)

SQL 数据库代码已经存在很长时间了，因为在计算机出现小写之前，所以历史上对大写关键字有偏好，但我更喜欢可读性而不是传统......（我现在使用颜色代码的每个工具都将关键字反正）

I also would use Table aliases to reduce the amount of text the eye has to scan in order to grok the structure of the query, as long as the aliases do not create confusion. In a query with less than 3 or 4 tables, Single character aliases are fine, I often use first letter of the table if all ther tables start with a different letter... again, whatever most contributes to readability. Finally, if your database supports it, many of the keywords are optional, (like "Inner", "Outer", "As" for aliases, etc.) "Into" (from Insert Into) is optional on Sql Server - but not on Oracle) So be careful about using this if your code needs to be platform independant...

我还会使用表别名来减少眼睛为了理解查询结构而必须扫描的文本量，只要别名不会造成混淆。在少于 3 或 4 个表的查询中，单字符别名很好，如果所有表以不同的字母开头，我经常使用表的第一个字母......同样，无论最有助于可读性。最后，如果您的数据库支持它，许多关键字是可选的，（如别名的“内部”、“外部”、“As”等）“Into”（来自 Insert Into）在 Sql Server 上是可选的 - 但不是在 Oracle 上）因此，如果您的代码需要独立于平台，请小心使用它...

Your example, I would write as:

你的例子，我会写成：

Select column1, column2
From table1 T1
Where column3 In (Select Top(1) column4
                  From table2 T2
                     Join table3 T3
                         On T2.column1 = T3.column1)

Or

或者

Select column1, column2
From table1 T1
Where column3 In 
     (Select Top(1) column4
      From table2 T2
         Join table3 T3
            On T2.column1 = T3.column1)

If there many more columns on the select clause, I would indent the second and subsequent lines... I generally do NOT adhere to any strict (one column per row) kind of rule as scrolling veritcally is almost as bad for readability as scrolling horizontally is, especially if only the first ten columns of the screen have any text in them)

如果 select 子句中有更多列，我会缩进第二行和后续行......我通常不遵守任何严格的（每行一列）规则，因为垂直滚动几乎与水平滚动一样糟糕是，特别是如果屏幕的前十列中有任何文本）

Select column1, column2, Col3, Col4, column5,
    column6, Column7, isNull(Column8, 'FedEx') Shipper,
    Case Upper(Column9) 
       When 'EAST'  Then 'JFK'
       When 'SOUTH' Then 'ATL'
       When 'WEST'  Then 'LAX'
       When 'NORTH' Then 'CHI' End HubPoint
From table1 T1
Where column3 In 
     (Select Top(1) column4
      From table2 T2
         Join table3 T3
            On T2.column1 = T3.column1)

Format the code in whatever manner makes it the most readable...

以任何方式格式化代码使其最易读......

Answer 10

回答by José Américo Antoine Jr

Example indenting a very very very complex SQL:

缩进非常非常复杂的 SQL 的示例：

SELECT 
    produtos_cesta.cod_produtos_cesta, 
    produtos.nome_pequeno,
    tab_contagem.cont,
    produtos_cesta.sku, 
    produtos_kits.sku_r AS sku_kit, 
    sku_final = CASE
        WHEN produtos_kits.sku_r IS NOT NULL THEN produtos_kits.sku_r
        ELSE produtos_cesta.sku
    END,
    estoque = CASE
        WHEN produtos2.estoque IS NOT NULL THEN produtos2.estoque
        ELSE produtos.estoque
    END,
    produtos_cesta.unidades as unidades1, 
    unidades_x_quantidade = CASE
        WHEN produtos.cod_produtos_kits_tipo = 1 THEN CAST(produtos_cesta.quantidade * (produtos_cesta.unidades / tab_contagem.cont) * produtos_kits.quantidade AS int)
        ELSE CAST(produtos_cesta.quantidade * produtos_cesta.unidades AS int)
    END,
    unidades = CASE
        WHEN produtos.cod_produtos_kits_tipo = 1 THEN produtos_cesta.unidades / tab_contagem.cont * produtos_kits.quantidade
        ELSE produtos_cesta.unidades
    END,
    unidades_parent = produtos_cesta.unidades,
    produtos_cesta.quantidade,
    produtos.controla_estoque, 
    produtos.status
FROM 
    produtos_cesta 
INNER JOIN produtos 
    ON (produtos_cesta.sku = produtos.sku) 
INNER JOIN produtos_pacotes 
    ON (produtos_cesta.sku = produtos_pacotes.sku) 
INNER JOIN (
    SELECT 
        produtos_cesta.cod_produtos_cesta,
        cont = SUM(
            CASE
                WHEN produtos_kits.quantidade IS NOT NULL THEN produtos_kits.quantidade
                ELSE 1
            END
        )
    FROM 
        produtos_cesta 
    LEFT JOIN produtos_kits 
        ON (produtos_cesta.sku = produtos_kits.sku) 
    LEFT JOIN produtos 
        ON (produtos_cesta.sku = produtos.sku) 
    WHERE 
        shopper_id = '" + mscsShopperId + @"' 
    GROUP BY 
        produtos_cesta.cod_produtos_cesta, 
        produtos_cesta.sku, 
        produtos_cesta.unidades 
) 
AS tab_contagem
    ON (produtos_cesta.cod_produtos_cesta = tab_contagem.cod_produtos_cesta)
LEFT JOIN produtos_kits 
    ON (produtos.sku = produtos_kits.sku) 
LEFT JOIN produtos as produtos2
    ON (produtos_kits.sku_r = produtos2.sku) 
WHERE 
    shopper_id = '" + mscsShopperId + @"' 
GROUP BY 
    produtos_cesta.cod_produtos_cesta, 
    tab_contagem.cont,
    produtos_cesta.sku, 
    produtos_kits.sku_r, 
    produtos.cod_produtos_kits_tipo, 
    produtos2.estoque,
    produtos.controla_estoque, 
    produtos.estoque, 
    produtos.status, 
    produtos.nome_pequeno, 
    produtos_cesta.unidades, 
    produtos_cesta.quantidade,
    produtos_kits.quantidade
ORDER BY 
    produtos_cesta.sku, 
    produtos_cesta.unidades DESC

SQL 语句缩进的好习惯

提问by MagicAndi

采纳答案by Patrick Desjardins

This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

对于不使用这种类型的编写 SQL 语句的人来说，这是一个示例。两者都包含缺少逗号的错误。

回答by Bill the Lizard

回答by Codewerks

回答by jalbert

回答by Slapout

回答by LeppyR64

回答by Mike Burton

回答by DOK

回答by Charles Bretana

回答by José Américo Antoine Jr

相关推荐

最近更新

标签

SQL 语句缩进的好习惯

提问by MagicAndi

采纳答案by Patrick Desjardins

This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

对于不使用这种类型的编写 SQL 语句的人来说，这是一个示例。两者都包含缺少逗号的错误。

回答by Bill the Lizard

回答by Codewerks

回答by jalbert

回答by Slapout

回答by LeppyR64

回答by Mike Burton

回答by DOK

回答by Charles Bretana

回答by José Américo Antoine Jr

相关推荐

是否可以从 SQL 查询执行文本文件？

为什么有人会在 SQL 子句中使用 WHERE 1=1 AND <conditions>？

在 SELECT 语句之后需要行计数：最佳 SQL 方法是什么？

SQL 无需索引即可提高 oracle 查询性能

相关推荐

最近更新

标签