数据量大，也是有助于查找问题的。

来源：互联网发布：广联达预算软件官网编辑：程序博客网时间：2024/05/01 23:22

数据量大，不可否认管理和维护起来难度都会有所增加。但是我们应该看到另一个好的方面，数据量大了，也是有助于查找问题的。下面是论坛里一个朋友发帖子问的问题：

现有一张表表名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

我看到一个朋友的回帖，内容如下：

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> create table tabel1(khmc varchar2(10),pzm varchar2(10),gx varchar2(10),sl number);Table created.SQL> insert into tabel1 values('AA','A-01','S1',2);1 row created.SQL> insert into tabel1 values('AA','A-01','S1',4);1 row created.SQL> insert into tabel1 values('AA','A-01','S1',8);1 row created.SQL> insert into tabel1 values('AA','A-01','S3',1);1 row created.SQL> insert into tabel1 values('AA','A-01','S3',1);1 row created.SQL> insert into tabel1 values('AA','A-01','S3',5);1 row created.SQL> commit;Commit complete.SQL> insert into tabel1 values('AA','A-01','S2',0);1 row created.SQL> commit;Commit complete.SQL> select * from tabel1;KHMC PZM GX SL---------- ---------- ---------- ----------AA A-01 S1 2AA A-01 S1 4AA A-01 S1 8AA A-01 S3 1AA A-01 S3 1AA A-01 S3 5AA 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 14AA A-01 S2 0AA A-01 S3 7--下面是我改过了的写法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 14AA A-01 S2 0AA A-01 S3 7

这样看起来好像结果没什么不同哦，可后来我又仔细看了下上面的SQL语句，又看了下楼主的要求里有这么一句话"GX要全部显示出来没有的现实0 ”。就加了点数据，又试了试，果然就试出来问题了

SQL> insert into tabel1 values('BB','B-01','S1',4);1 row created.SQL> insert into tabel1 values('BB','B-01','S1',9);1 row created.SQL> insert into tabel1 values('BB','B-01','S2',2);1 row created.SQL> insert into tabel1 values('BB','B-01','S2',4);1 row created.SQL> commit;Commit complete.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 14AA A-01 S2 0AA A-01 S3 7BB B-01 S1 13BB B-01 S2 6BB 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 14AA A-01 S2 0AA A-01 S3 7BB B-01 S1 13BB B-01 S2 6

看到了吧，BB中没有显示出S3相应的记录。

这也就是前面标注出红色那部分我一开始没看明白语句的作用

通过第二句在两个select语句中选取出了khmc,pzm和gx，如果一起选的话，就不会有

BB B-01 S3 0

这条记录了。

而where条件中的(+)作用就显而易见了，外连接，即便BB中不存在相应S3的记录，也要拼接出一条来，通过NVL把SL字段置为0就可以了。