-- 为了偷懒想使用grouping sets,但是正确打开该函数,还是有难度的
-- 1. 不可以有多个表格相同列名的情况;如果多表格列名相同的,需更改;uid、col_a、col_b都需要改成不同的名字
-- 2. 不可以在select之后有表格别名的情况,比如a.col_a_1是不允许的;但是因为列名都不同,没有关系
-- 3. select之后用来aggregate(聚合)的字段,不可以进入计算;所以需要新加入col_b_1_1,而不是直接使用 col_b_1
-- 4. tips:如果不想有聚合字段,grouping sets后面可以加 ()空括弧
select col_a_1
, col_b_1
, count(distinct uid_1)
, count(distinct uid_2)
, count(distinct if(col_b_1_1 = 'some_string', uid_1, null))
-- 不可以是col_b_1
(
select uid as uid_1
, col_a as col_a_1
, col_b as col_b_1
, col_b as col_b_1_1
from table_1
) a
left join
(
select uid as uid_2
, col_a as col_a_2
, col_b as col_b_2
from table_2
) b
on a.uid_1 = b.uid_2
and a.col_a_1 = b.col_a_2
and a.col_b_1 = b.col_b_2
group by col_a_1
, col_b_1
grouping sets
(col_a_1, col_b_1, (), (col_a_1, col_b_1))
;