1 WITH tb AS(
2 SELECT 'A' 商品名,'D1' 销售员,2 数量,2 单价,'A' 规格,To_Date('2010.8.31','yyyy.mm.dd') 时间
3 FROM DUAL UNION ALL
4 SELECT 'B', 'D1', 6, 3, 'B', To_Date('2010.8.1','yyyy.mm.dd') FROM DUAL UNION ALL
5 SELECT 'C', 'D2', 4, 4, 'C', To_Date('2010.8.6','yyyy.mm.dd') FROM DUAL UNION ALL
6 SELECT 'B', 'D2', 2, 3, 'B', To_Date('2010.9.4','yyyy.mm.dd') FROM DUAL UNION ALL
7 SELECT 'C', 'D3', 5, 4, 'C', To_Date('2010.8.21','yyyy.mm.dd') FROM DUAL UNION ALL
8 SELECT 'A', 'D3', 3, 2, 'A', To_Date('2010.8.2','yyyy.mm.dd') FROM DUAL UNION ALL
9 SELECT 'A', 'D3', 4, 2, 'A', To_Date('2010.8.14','yyyy.mm.dd') FROM DUAL UNION ALL
10 SELECT 'E', 'D1', 3, 3, 'E', To_Date('2010.8.7','yyyy.mm.dd') FROM DUAL UNION ALL
11 SELECT 'E', 'D3', 5, 3, 'E', To_Date('2010.8.25','yyyy.mm.dd') FROM DUAL
12 )
13 select 商品名,销售员,时间,单价,规格,数量汇总
14 from
15 (select 商品名,nvl(销售员,商品名
------解决方案--------------------'总计') 销售员,to_char(时间,'yyyymm') 时间,
16 grouping(to_char(时间,'yyyymm')) sj,
17 grouping(销售员) pm,
18 max(单价) 单价,max(规格) 规格,sum(数量) 数量汇总
19 from tb
20 group by rollup(商品名,to_char(时间,'yyyymm'),销售员))
21* where (sj,pm) in((0,0),(0,1))
SQL> /
销售 时间 单价 数量汇总
- ----- ------ ---------- - ----------
A D1 201008 2 A 2
A D3 201008 2 A 7
A A总计 201008 2 A 9
B D1 201008 3 B 6
B B总计 201008 3 B 6
B D2 201009 3 B 2
B B总计 201009 3 B 2
C D2 201008 4 C 4
C D3 201008 4 C 5