sql server ¹ØÓÚ Êý¾Ý»ã×Ü·ÖÎö

CUBE ºÍ ROLLUP Ö®¼äµÄÇø±ðÔÚÓÚ£º
CUBE Éú³ÉµÄ½á¹û¼¯ÏÔʾÁËËùÑ¡ÁÐÖÐÖµµÄËùÓÐ×éºÏµÄ¾ÛºÏ¡£
ROLLUP Éú³ÉµÄ½á¹û¼¯ÏÔʾÁËËùÑ¡ÁÐÖÐÖµµÄijһ²ã´Î½á¹¹µÄ¾ÛºÏ¡£
Àý×Ó£º
  if object_id('a')  is not null
drop table a
create table a
(
  bm varchar(20),  --±àÂë
  ck varchar(2),   --²Ö¿â
  sl int           --ÊýÁ¿
)
insert into a
select '01' ,'a', 6
union all
select '01','b',7
union all
select '02','a',8
union all
select '02','b',9
select * from a
£¨1£©rollup µÄÓ÷¨
SELECT bm, hj=
case when grouping(ck)=0
then
  ck
else
  bm+'ºÏ¼Æ'
end
, Sum(sl) as sl
from A
GROUP BY bm, ck WITH ROLLUP
/*
01       a      6
      01       b      7
      01   01ºÏ¼Æ 13
      02       a      8
      02        b     9
      02   02ºÏ¼Æ  17
     NULL NULL  30
*/
£¨2£©¹ØÓÚcubleµÄ»ù±¾Ó÷¨¡£
SELECT bm, ck, Sum(sl) as sl
from A
GROUP BY bm,ck WITH cube
 
 
 
 
 
 
/*
 
01          a                     6
      01          b                      7
      01         NULL