在SQL Server 2012中如何使用分组集(二)

4. GROUPING和GROUPING_ID函数

当有一个定义了多个分组集的单独查询时，你可能需要能够关联结果行与分组集，即确定与每个结果行相关联的分组集。只要所有分组元素定义为NOT NULL，这是很容易的。例如，请考虑下面的查询。

SELECT empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY CUBE(empid, custid);

此查询会生成以下输出。

empid custid sumqty
----------- --------- -----------
2 A 52
3 A 20
NULL A 72
1 B 20
2 B 27
NULL B 47
1 C 34
3 C 22
NULL C 56
3 D 30
NULL D 30
NULL NULL 205
1 NULL 54
2 NULL 79
3 NULL 72

因为empid和custid列在dbo.Orders中定义为NOT NULL，这些列中的NULL仅代表一个占位符，指示该列没有参与当前的分组集。那么，例如，empid不为NULL且custid不为NULL的所有行是与分组集(empid, custid)相关联的，empid不为NULL且custid为NULL的所有行是与分组集(empid)相关联的，依此类推。某些人以ALL或类似占位符覆盖NULL标志，就是要原始列不为空，这对报表是有帮助的。

但是，如果分组列在表中定义为允许NULL标记，你就不能确定结果集中的NULL是源自数据还是一个不参与分组集成员的占位符。一种能够判断与分组集相关联的确定方式（即使分组列允许NULL标记），是使用GROUPING函数。此函数接受一个列名称，如果该列是当前分组集的成员，返回0，否则返回1。

注意我觉得反常的是，GROUPING函数在元素不是分组集成员时返回1，在是的时候返回0。对我来说，在元素是分组集的成员时返回1（即true），不是时返回0，这样会更加清晰。不过，函数就是这么实施的，所以你只需确保已经意识到这一问题即可。

例如，下面的查询为每个分组元素调用了GROUPING函数。

SELECT

 GROUPING(empid) AS grpemp,

 GROUPING(custid) AS grpcust,  empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY CUBE(empid, custid);

此查询返回下面的输出。

grpemp grpcust empid custid sumqty
--------- ---------- ----------- --------- -----------
0 0 2 A 52
0 0 3 A 20
1 0 NULL A 72
0 0 1 B 20
0 0 2 B 27
1 0 NULL B 47
0 0 1 C 34
0 0 3 C 22
1 0 NULL C 56
0 0 3 D 30
1 0 NULL D 30
1 1 NULL NULL 205
0 1 1 NULL 54
0 1 2 NULL 79
0 1 3 NULL 72

现在你再也不需要依靠NULL标记来判断结果行和分组集之间的相关性了。例如，grpemp为0且grpcust为0的所有行是与分组集(empid, custid)相关联的，grpemp为0且grpcust为1的所有行是与分组集(empid)相关联的，依此类推。

SQL Server支持另一个名为GROUPING_ID的函数，可以进一步简化结果行与分组集的关联处理。将参与任何分组集的所有元素作为函数的输入，例如GROUPING_ID(a, b, c, d)，该函数返回一个整数位图，其中每位代表一个不同的输入元素，最右边的元素由最右边的位表示。例如，分组集(a, b, c, d)用整数0表示（即0×8+0×4+0×2+0×1），分组集(a, c)用整数5表示（即0×8+1×4+0×2+1×1），依此类推。

作为对之前查询中对每个分组元素调用GROUPING函数的替代，可以调用GROUPING_ID函数一次将所有分组元素提供给它作为输入，如下所示。

SELECT

 GROUPING_ID(empid, custid) AS groupingset,   empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders

GROUP BY CUBE(empid, custid);

此查询会生成下面的输出。

groupingset empid custid sumqty
-------------- ----------- --------- -----------
0 2 A 52
0 3 A 20
2 NULL A 72
0 1 B 20
0 2 B 27
2 NULL B 47
0 1 C 34
0 3 C 22
2 NULL C 56
0 3 D 30
2 NULL D 30
3 NULL NULL 205
1 1 NULL 54
1 2 NULL 79
1 3 NULL 72

现在你可以轻松地找出与分组集相关的每一行。整数0（二进制00）代表分组集(empid, custid)，整数1（二进制01）代表(empid)，整数2（二进制10）代表(custid)，整数3（二进制11）代表()。