现有一张表 表名tabel1
KHMC PZM GX SL
AA A-01 S1 2
AA A-01 S3 1
AA A-01 S1 4
AA A-01 S3 1
AA A-01 S1 8
AA A-01 S3 5
BB B-01 S1 4
BB B-01 S2 2
BB B-01 S1 9
BB B-01 S2 4
BB B-01 S1 4
BB B-01 S2 2 请帮忙写 汇总要求格式 按 KHMC PZM 汇总 GX要全部显示出来 没有的现实0
KHMC PZM GX SL
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7
BB B-01 S1 17
BB B-01 S2 8
BB B-01 S3 0
KHMC PZM GX SL
AA A-01 S1 2
AA A-01 S3 1
AA A-01 S1 4
AA A-01 S3 1
AA A-01 S1 8
AA A-01 S3 5
BB B-01 S1 4
BB B-01 S2 2
BB B-01 S1 9
BB B-01 S2 4
BB B-01 S1 4
BB B-01 S2 2 请帮忙写 汇总要求格式 按 KHMC PZM 汇总 GX要全部显示出来 没有的现实0
KHMC PZM GX SL
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7
BB B-01 S1 17
BB B-01 S2 8
BB B-01 S3 0
FROM table1
group by KHMC,PZM
(select * from (select distinct khmc,pzm from tabel1) a,(select distinct gx from tabel1) b) a,
(select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
where a.khmc = b.khmc(+) and a.pzm = b.pzm(+) and a.gx = b.gx(+)
order by 1,2,3;
---------- ---------- ----- ----------
AA A-01 S1 2
AA A-01 S3 1
AA A-01 S1 4
AA A-01 S3 1
AA A-01 S1 8
AA A-01 S3 5
BB B-01 S1 4
BB B-01 S2 2
BB B-01 S1 9
BB B-01 S2 4
BB B-01 S1 4
BB B-01 S2 2已选择12行。已用时间: 00: 00: 00.00
09:26:16 scott@TUNGKONG> select a.khmc,a.pzm,a.gx,nvl(num,0) from
09:26:18 2 (select * from (select distinct khmc,pzm from tabel1) a,(select distinct gx from tabel1) b) a,
09:26:18 3 (select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
09:26:18 4 where a.khmc = b.khmc(+) and a.pzm = b.pzm(+) and a.gx = b.gx(+)
09:26:18 5 order by 1,2,3;KHMC PZM GX NVL(NUM,0)
---------- ---------- ----- ----------
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7
BB B-01 S1 17
BB B-01 S2 8
BB B-01 S3 0已选择6行。已用时间: 00: 00: 00.03
FROM
(
select KHMC,PZM,GX,SL from table1
UNION ALL
SELECT 'AA' KHMC , 'A-01' PZM ,'S2' GX, 0 SL FROM DUAL
)
group by KHMC,PZM
给你的表UNION上条记录就好 了,简易做法,仅供参考
select a.khmc,a.pzm,a.gx,nvl(num,0) from
(select * from (select distinct khmc,pzm from tabel1) a,(select distinct gx from tabel1) b) a,
(select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
where a.khmc = b.khmc(+) and a.pzm = b.pzm(+) and a.gx = b.gx(+)order by 1,2,3;红色标出来的地方有什么特别的作用吗?
SQL> select * from tabel1;KHMC PZM GX SL
---------- ---------- ---------- ----------
AA A-01 S1 2
AA A-01 S1 4
AA A-01 S1 8
AA A-01 S3 1
AA A-01 S3 1
AA A-01 S3 5
AA A-01 S2 07 rows selected.SQL> select a.khmc,a.pzm,a.gx,nvl(num,0) from
2 (select * from (select distinct khmc,pzm from tabel1) a,(select distinct gx
from tabel1) b) a,
3 (select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
4 where a.khmc = b.khmc(+) and a.pzm = b.pzm(+) and a.gx = b.gx(+)
5 order by 1,2,3;KHMC PZM GX NVL(NUM,0)
---------- ---------- ---------- ----------
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7SQL> select a.khmc,a.pzm,a.gx,nvl(num,0) from
2 (select distinct khmc,pzm,gx from tabel1) a,
3 (select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
4 where a.khmc = b.khmc and a.pzm = b.pzm and a.gx = b.gx
5 order by 1,2,3;KHMC PZM GX NVL(NUM,0)
---------- ---------- ---------- ----------
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7SQL>
2 (select * from (select distinct khmc,pzm from tabel1) a,(select distinct gx
from tabel1) b) a,
3 (select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
4 where a.khmc = b.khmc(+) and a.pzm = b.pzm(+) and a.gx = b.gx(+)
5 order by 1,2,3;KHMC PZM GX NVL(NUM,0)
---------- ---------- ---------- ----------
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7
BB B-01 S1 13
BB B-01 S2 6
BB B-01 S3 06 rows selected.
SQL> select a.khmc,a.pzm,a.gx,nvl(num,0) from
2 (select distinct khmc,pzm,gx from tabel1) a,
3 (select khmc,pzm,gx,sum(sl) num from tabel1 group by khmc,pzm,gx) b
4 where a.khmc = b.khmc and a.pzm = b.pzm and a.gx = b.gx
5 order by 1,2,3;KHMC PZM GX NVL(NUM,0)
---------- ---------- ---------- ----------
AA A-01 S1 14
AA A-01 S2 0
AA A-01 S3 7
BB B-01 S1 13
BB B-01 S2 6明白了……加了点BB的记录,试出不同来了。呵呵