table
a b c d
0001 0101 0005 200.00
0001 0102 0008 500.00
0001 0101 0009 300.00
0001 0102 0008 400.00
group by a,b,分组后求和sum(d)结果:
0001 0101 0005 200.00
0001 0101 0009 300.00
0001 0101 500.00(和值)
0001 0102 0008 900.00
0001 0102 900.00(和值)
0001 1400.00大概就这意思吧,反正能得到各个分组的和值!
a b c d
0001 0101 0005 200.00
0001 0102 0008 500.00
0001 0101 0009 300.00
0001 0102 0008 400.00
group by a,b,分组后求和sum(d)结果:
0001 0101 0005 200.00
0001 0101 0009 300.00
0001 0101 500.00(和值)
0001 0102 0008 900.00
0001 0102 900.00(和值)
0001 1400.00大概就这意思吧,反正能得到各个分组的和值!
---- ---- ---- ----------------------------------------
0001 0101 0005 200.00
0001 0101 0009 300.00
0001 0101 NULL 500.00
0001 0102 0008 900.00
0001 0102 NULL 900.00
0001 NULL NULL 1400.00
NULL NULL NULL 1400.00
insert into @t select '0001','0101','0005',200.00
insert into @t select '0001','0102','0008',500.00
insert into @t select '0001','0101','0009',300.00
insert into @t select '0001','0102','0008',400.00
select a,b,c,sum(d)
from @t
group by a,b,c with rollup/*count name age
----------- ---- -----------
1 a 11
2 b 22
3 c 33
4 d 44(所影响的行数为 4 行)*/
insert into @t select '0001','0101','0005',200.00
insert into @t select '0001','0102','0008',500.00
insert into @t select '0001','0101','0009',300.00
insert into @t select '0001','0102','0008',400.00
select a,b,c,sum(d)
from @t
group by a,b,c with rollup/*a b c
---- ---- ---- ----------------------------------------
0001 0101 0005 200.00
0001 0101 0009 300.00
0001 0101 NULL 500.00
0001 0102 0008 900.00
0001 0102 NULL 900.00
0001 NULL NULL 1400.00
NULL NULL NULL 1400.00*/
insert into @t select '0001','0101','0005',200.00
insert into @t select '0001','0102','0008',500.00
insert into @t select '0001','0101','0009',300.00
insert into @t select '0001','0102','0008',400.00
select CASE WHEN (GROUPING(a) = 1) THEN 'all'
ELSE ISNULL(a, 'UNKNOWN')
END AS a,CASE WHEN (GROUPING(b) = 1) THEN 'all'
ELSE ISNULL(b, 'UNKNOWN')
END AS b,
CASE WHEN (GROUPING(c) = 1) THEN 'all'
ELSE ISNULL(c, 'UNKNOWN')
END AS c,sum(d)
from @t
group by a,b,c with rollup/*a b c
---- ---- ---- ----------------------------------------
0001 0101 0005 200.00
0001 0101 0009 300.00
0001 0101 all 500.00
0001 0102 0008 900.00
0001 0102 all 900.00
0001 all all 1400.00
all all all 1400.00*/