在所有情况下，SQL GROUP BY字段都是可交换的吗？

In a simple query the order of your GROUP BY fields makes no difference (ignoring developer legibility) to the final result.

在一个简单的查询中，GROUP BY字段的顺序与最终结果没有区别（忽略开发人员的易读性）。

EG: SELECT COUNT(*) FROM People GROUP BY Age, Gender will produce the same results as if the GROUP BY fields were flip-flopped.

EG：SELECT COUNT（*）FROM People GROUP BY Age，Gender将产生相同的结果，就像GROUP BY字段被翻转一样。

Generally speaking, under what condition(s) does this apparent commutative property of the GROUP BY fields break down?

一般来说，在什么条件下，GROUP BY字段的这种明显的可交换属性会崩溃？

I'm looking for a general rule here (EG: "Any expression containing sub-expressions which depend upon one of the grouped fields")

我在这里寻找一般规则（EG：“包含依赖于其中一个分组字段的子表达式的任何表达式”）

I'm having a hard time coming up with an example of where the ordering would matter - but my gut tells me it does in some situation(s).

我很难想出一个关于排序重要的例子 - 但我的直觉告诉我它在某些情况下确实如此。

4 个解决方案

#1

I think the only time it matters is when using ROLLUP to create subtotals

我认为唯一重要的是使用ROLLUP来创建小计

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

CREATE TABLE #Prod
(
    ID INT IDENTITY(1,1),
    Cat INT,
    Size Int
)

INSERT #Prod SELECT 1,1
INSERT #Prod SELECT 1,1
INSERT #Prod SELECT 1,2
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 1,3
INSERT #Prod SELECT 2,1
INSERT #Prod SELECT 2,2
INSERT #Prod SELECT 2,2
INSERT #Prod SELECT 2,3
INSERT #Prod SELECT 2,3
INSERT #Prod SELECT 2,3

SELECT 
COUNT(*)
FROM #Prod
GROUP BY Cat, Size WITH ROLLUP

SELECT 
COUNT(*)
FROM #Prod
GROUP BY Size , Cat WITH ROLLUP

Results from Query 1

2 1 3 6 1 2 3 6 12

2 1 3 6 1 2 3 6 12

(9 row(s) affected)

（9行受影响）

Results from Query 2

2 1 3 1 2 3 3 3 6 12

2 1 3 1 2 3 3 3 6 12

(10 row(s) affected)

（10排受影响）

#2

I am only speculating here, but it might be possible that if someone implements CLR based aggregate functions, that the order would matter. I've implemented a aggregate function before in C# and I have this feeling that depending on what the aggregation is actually doing, that there may be a chance that the order of the group by may effect it.

我只是在这里推测，但如果有人实现基于CLR的聚合函数，那么订单可能很重要。我之前在C＃中实现了一个聚合函数，我有这种感觉，根据聚合实际上做的事情，组可能会影响它的顺序。

I don't know enough about how aggregate CLR functions interact with the engine to really say anything more than that :/

我不太了解聚合CLR函数如何与引擎交互以真正说出更多：/

#3

I like your question :)

我喜欢你的问题:)

I think in the case you mention, where you are doing counts (which is purely additive), the order of grouping doesn't matter. Addition is commutative and GROUP BY should not remove any rows from the set, it just aggregates them for display. Therefore, it shouldn't make any difference what order you group them in.

我认为，在你提到的情况下，你在做计数（纯粹是加法），分组的顺序无关紧要。添加是可交换的，GROUP BY不应该从集合中删除任何行，它只是聚合它们以供显示。因此，将它们分组的顺序不应该有任何区别。

#4

NEW!!! So... it's possible in the use of grouping sets, order may matter; i'd have to do some testing.

新！！！所以...在使用分组集时，订单可能很重要;我必须做一些测试。

---OLD yeah ORDER <> Group. Given:

---老是啊订购<>集团。鉴于：

Select val, text2 from b
order by text2, val;

RESULT:

结果：

   Val  text2
    4   XXX010103
    1   XXX010105
    1   something XXX010101 something else XXX010102
    2   yet another XXX010102 and this XXX010103

vs:

VS：

Select val, text2 from b
order by val,text2;

  val   text2
    1   XXX010105
    1   something XXX010101 something else XXX010102
    2   yet another XXX010102 and this XXX010103
    4   XXX010103

the different order by's show a different sort; but maybe the question is not about the order but the subset: and again yes order matters in different subsets:

不同的顺序显示了不同的排序;但也许问题不是关于顺序而是关于子集：并且在不同的子集中，命令也很重要：

Imagine select top 1 from table order by val vs select top 1 from table order by val desc;