SQL group by 子句中的列顺序是否重要？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3064677/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does the order of columns matter in a group by clause?
提问by Jeff Meatball Yang
If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by?
如果我有两列，一列具有非常高的基数，另一列具有非常低的基数（唯一的值数），我按哪个顺序分组是否重要？
Here's an example:
下面是一个例子：
select
dimensionName,
dimensionCategory,
sum(someFact)
from SomeFact f
join SomeDim d on f.dimensionKey = d.dimensionKey
group by
d.dimensionName, -- large number of unique values
d.dimensionCategory -- small number of unique values
Are there situations where it matters?
是否存在重要的情况？
采纳答案by OMG Ponies
No, the order doesn't matter for the GROUP BY clause.
不，顺序对于 GROUP BY 子句无关紧要。
MySQL and SQLite are the only databases I'm aware of that allow you to select columns which are omitted from the group by (non-standard, not portable) but the order doesn't matter there either.
MySQL 和 SQLite 是我所知道的唯一允许您选择从 group by （非标准，不可移植）中省略的列的数据库，但顺序也不重要。
回答by gbn
SQL is declarative.
SQL 是声明性的。
In this case, you have told the optimiser how you want the data grouped and it works out how to do it.
在这种情况下，您已经告诉优化器您希望如何对数据进行分组，并确定如何进行分组。
It won't evaluate line by line (procedural) and look at one column first
它不会逐行评估（程序性）并首先查看一列
The main place column order matters is for indexes. col1, col2is not the same as col2, col1. At all.
主要的列顺序问题是索引。col1, col2不一样col2, col1。在所有。
回答by nvogel
There's a legacy, non-standard feature of Microsoft SQL Server called ROLLUP. ROLLUP is an extension to the GROUP BY syntax and when it is used the order of the GROUP BY columns determines which columns should be grouped in the result. ROLLUP is deprecated however. The standard SQL alternative is to use grouping sets, which is supported by SQL Server 2008 and later versions.
Microsoft SQL Server 有一个传统的非标准功能，称为 ROLLUP。ROLLUP 是 GROUP BY 语法的扩展，当使用它时，GROUP BY 列的顺序决定了结果中应该对哪些列进行分组。然而，不推荐使用 ROLLUP。标准的 SQL 替代方法是使用 SQL Server 2008 及更高版本支持的分组集。
回答by AaCodes
Since this has not been mentioned here. The answers above are correct i.e. the order of the columns after the "group by" clause will not affect the correctnessof the query (i.e. the sum amount).
由于这里没有提到这一点。上面的答案是正确的，即“group by”子句后的列顺序不会影响查询的正确性（即总和）。
However, the order of the rows being retrieved will vary based on the order of the columns specified after the "group by" clause. For example consider Table Awith the following rows:
但是，检索的行的顺序将根据“group by”子句后指定的列的顺序而有所不同。例如，考虑A具有以下行的表：
Col1 Col2 Col3
1 xyz 100
2 abc 200
3 xyz 300
3 xyz 400
SELECT *, SUM(Col3) FROM A GROUP BY Col2, Col1will retrieve rows ordered by the Col2in ascending order.
SELECT *, SUM(Col3) FROM A GROUP BY Col2, Col1将检索Col2按升序排列的行。
Col1 Col2 Col3 sum(Col3)
2 abc 200 200
1 xyz 100 100
3 xyz 300 700
Now change the ordering of column in group by to Col1, Col2. The retrieved rows are ordered asc by Col1.
现在将 group by 中列的顺序更改为Col1, Col2。检索到的行按 asc 排序Col1。
i.e. select *, sum(Col3) from A group by Col1, Col2
IE select *, sum(Col3) from A group by Col1, Col2
Col1 Col2 Col3 sum(Col3)
1 xyz 100 100
2 abc 200 200
3 xyz 300 700
Note: The the summation amount (i.e. the correctness of the query) remains exactly the same.
注意：求和量（即查询的正确性）保持完全相同。
回答by Gauravk
If I have two columns, one with very high cardinality and one with very low cardinality (unique # of values), does it matter in which order I group by?
如果我有两列，一列具有非常高的基数，另一列具有非常低的基数（唯一的值数），我按哪个顺序分组是否重要？
Query-1
查询 1
SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt FROM tbl_product_spec
GROUP BY spec_id, catid, spec_display_value ;
Query-2
查询 2
SELECT spec_id, catid, spec_display_value, COUNT(*) AS cnt FROM tbl_product_spec FORCE INDEX(idx_comp_spec_cnt)
GROUP BY catid, spec_id,spec_display_value;
Both are equal , order doesn't work in group by clause.
两者是相等的， order 在 group by 子句中不起作用。
