在使用“group by ... with rollup”时区分NULL [英] Distinguish between NULL's when using "group by ... with rollup"
本文介绍了在使用“group by ... with rollup”时区分NULL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我使用 group by ...与汇总
运行查询时:
When I run a query using group by ... with rollup
:
select a, b, sum(c)
from <table>
group by a, b with rollup;
我在查询中获取重复行(我认为是)PK(也就是说, group-by列):
I get duplicate rows in (what I consider to be) the PK of the query (that is, the group-by columns):
+------+------+--------+
| 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 |
...更多行关注...
... more rows follow ...
如何从(NULL,NULL,34)
区分(NULL,NULL,13)
>?也就是说,如何区分由于基础数据而导致空值的行与因汇总而添加的空值的行? (注意,还有更多的例子 - (2,NULL,6)
和(2,NULL,40)
)
推荐答案
好的问题。我可以想到的一个选择是这样做的:
Good question. One option I can think of is to do this:
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;
这篇关于在使用“group by ... with rollup”时区分NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文