Contents

1 蠍磯蓋 覦覯
2 一危 觧ロ蠍
3 谿瑚襭


1 蠍磯蓋 覦覯 #

ROLLUP螻 CUBE襯 伎 貎朱Μ れ螻 螳 蟆郁骸襯 訖碁. 2005 覯 危 企.
--Grouping Set
use tempdb
go

--drop table grp_set_test
create table grp_set_test(
	group1 int
,	group2 char(1)
,	val int
);

insert grp_set_test values 
	(1, 'a', 10)
,	(1, 'a', 20)
,	(1, 'a', 30)
,	(1, 'b', 60)
,	(2, 'a', 70)
,	(2, 'a', 80)
,	(3, 'c', 70)
,	(3, 'c', 90);
go

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with rollup;
/*
group1      group2 total
----------- ------ -----------
1           a      60
1           b      60
1           NULL   120
2           a      150
2           NULL   150
3           c      160
3           NULL   160
NULL        NULL   430
*/

select
	group1
,	group2 
,	SUM(val) as total
from grp_set_test
group by 
	group1
,	group2 with cube;
/*
group1      group2 total
----------- ------ -----------
1           a      60
2           a      150
NULL        a      210
1           b      60
NULL        b      60
3           c      160
NULL        c      160
NULL        NULL   430
1           NULL   120
2           NULL   150
3           NULL   160
*/

2008覯 れ螻 螳 . 貎朱Μ 螳 蟆郁骸襯 訖襴磯.
--sql server 2008 new syntax
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	rollup(group1, group2); --new syntax
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets --new syntax
	(
		(group1, group2)
	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
total  total  3            430
*/

蟆郁骸 螻 豐螻襯 蟇壱 覲伎.
--螻 蟇
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
--	,	(group1)
	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
2      a      0            150
3      c      0            160
total  total  3            430
*/

--豐螻 蟇
select
	case when grouping(group1) = 1 then 'total' else convert(varchar(5), group1) end group1
,	case when grouping(group2) = 1 then 'total' else group2 end group2 
,	grouping_id(group1, group2) grouping_ids --new syntax
,	SUM(val) as total
from grp_set_test
group by 
	grouping sets
	(
		(group1, group2)
	,	(group1)
--	,	()
	);
/*
group1 group2 grouping_ids total
------ ------ ------------ -----------
1      a      0            60
1      b      0            60
1      total  1            120
2      a      0            150
2      total  1            150
3      c      0            160
3      total  1            160
*/
蟆郁骸 願 譬 譟.

谿瑚:
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

--is the same as
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year

2 一危 觧ロ蠍 #

一危磯ゼ 觧ロ蠍 る 朱 覦覯 dummy 企螻 cross join 蟇一. 讌襷 蟆曙一 磯殊 grouping sets襯 伎 . れ 襯 覲企 一危 觧ロ蠍壱 蟆 覲 .
;with dummy
as
(
        select 0 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 10
)
select
    width
,   cnt
from (
    select 
        isnull((case when seq between 1 and 3 then '1~3' end)
    ,   isnull((case when seq between 1 and 6 then '1~6' end)
    ,   (case when seq between 1 and 9 then '1~9' end))) width
    ,    count(*) cnt
    from dummy
    group by
        grouping sets
        (
            (case when seq between 1 and 3 then '1~3' end)
        ,   (case when seq between 1 and 6 then '1~6' end)
        ,   (case when seq between 1 and 9 then '1~9' end)
        )
) t
where width is not null
option (maxrecursion 0);

/* result 

width cnt
----- -----------
1~9   9
1~6   6
1~3   3

*/

3 谿瑚襭 #