|
当我使用group by ... with rollup查询以下命令:! z8 u) Z% G- S! |8 Z
select a,b,sum(c) from [table] group by a,b with rollup;我在查询的PK(即分组依据列)中的重复行(我认为是):, Q, t/ o- I# `; k7 v
------ ------ -------- | a | b | sum(c) | ------ ------ -------- | NULL | NULL | 13 || NULL | 1 | 4 || NULL | 3 | 8 || NULL | 4 | 9 || NULL | NULL | 34 || 1 | 3 | 17 || 1 | 4 | NULL || 1 | 17 | 2 || 1 | NULL | 19 || 2 | NULL | 6 || 2 | 1 | 17 || 2 | 3 | 17 || 2 | NULL | 40 || 4 | 17 | 2 || 4 | NULL | 2 || 5 | NULL | 11 || 5 | 6 | 7 || 5 | NULL | 18 || 13 | 4 | 2 || 13 | NULL | 2 || 14 | 41 | 3 || 14 | NULL | 3 || 18 | 1 | 2 || 18 | NULL | 2 || 41 | 2 | 17 || 41 | NULL | 17 |…更多行跟随…0 |* m1 }( t v. e \' o7 N
如何区分(NULL,NULL,13)从(NULL,NULL,34)?
# ?8 }* \ o0 M- L# q. N$ x9 R) O也就是说,如何区分因基础数据而为空的行和因汇总而添加为空的行?(请注意,还有更多的例子-(2,NULL,6)和(2,NULL,40)). G0 A3 ~$ w ?3 t" L6 v
% F% [$ O) o1 [* k" N+ {
解决方案: 2 W' O5 f, F# @6 Z+ U: T9 N
好问题。我能想到的一个选择是:0 h) j8 y: i* B: U8 [) |
select COALESCE(a,-1) AS a,COALESCE(b,-1) AS b,sum(c) from [table] group by COALESCE(a,-1),COALESCE(b,-1) with rollup; |
|