本人为做报表,今写如下SQL语句,哪位兄弟姐妹帮忙看下能不能优化下,这条语句执行有点慢。select xm as "姓名",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200701
group by gz.ryid) as "200701",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200702
group by gz.ryid) as "200702",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200703
group by gz.ryid) as "200703",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200704
group by gz.ryid) as "200704",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200705
group by gz.ryid) as "200705",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200706
group by gz.ryid) as "200706",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200707
group by gz.ryid) as "200707",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200708
group by gz.ryid) as "200708",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200709
group by gz.ryid) as "200709",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200710
group by gz.ryid) as "200710",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200711
group by gz.ryid) as "200711",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200712
group by gz.ryid) as "200712"
from ryjbqk r order by (select xssx from gg_jgbh where jgbh = substr(r.jgbh,1,8)),(select xssx from gg_jgbh where jgbh = substr(r.jgbh,1,12)),r.xssx本语句实现功能如下:查询出单位里所有员工某一年(从前台传入的参数)各个月的收入情况明细,好比如下二维表:姓名 一月份 二月份 三月份 四月份 五月份
张三 3000 3500 3400 3750 3550
。 。 。 。 。 。 。
。 。 。 。 。 。 。
。 。 。 。 。 。 。
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200701
group by gz.ryid) as "200701",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200702
group by gz.ryid) as "200702",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200703
group by gz.ryid) as "200703",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200704
group by gz.ryid) as "200704",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200705
group by gz.ryid) as "200705",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200706
group by gz.ryid) as "200706",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200707
group by gz.ryid) as "200707",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200708
group by gz.ryid) as "200708",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200709
group by gz.ryid) as "200709",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200710
group by gz.ryid) as "200710",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200711
group by gz.ryid) as "200711",
(select sum(nvl(yfgz, 0))
from gz
where ryid = r.ryid
and substr(ywrq, 1, 6) = 200712
group by gz.ryid) as "200712"
from ryjbqk r order by (select xssx from gg_jgbh where jgbh = substr(r.jgbh,1,8)),(select xssx from gg_jgbh where jgbh = substr(r.jgbh,1,12)),r.xssx本语句实现功能如下:查询出单位里所有员工某一年(从前台传入的参数)各个月的收入情况明细,好比如下二维表:姓名 一月份 二月份 三月份 四月份 五月份
张三 3000 3500 3400 3750 3550
。 。 。 。 。 。 。
。 。 。 。 。 。 。
。 。 。 。 。 。 。
select xm as "姓名",
max(case substr(t.ywrq, 1, 6) when 200701 then sum(nvl(t.yfgz, 0)) else 0 end) "200701"
max(case substr(t.ywrq, 1, 6) when 200702 then sum(nvl(t.yfgz, 0)) else 0 end) "200702"
......
max(case substr(t.ywrq, 1, 6) when 200712 then sum(nvl(t.yfgz, 0)) else 0 end) "200712"
from ryjbqk r
left join (select ywrq,yfgz)
from gz
where ryid = r.ryid
group by gz.ryid) t on t.ryid = r.ryid
order by (select xssx from gg_jgbh where jgbh = substr(r.jgbh, 1, 8)),
(select xssx from gg_jgbh where jgbh = substr(r.jgbh, 1, 12)),
r.xssx
group by xm
谢谢【mantisXF 】和【heyixiang】!
/*
按月进行行列转换并加合计(2007-11-19于海南三亚)例如有表tb某些人每月消费数据如下:
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果:
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212
*/create table tb
(
id char(3),
data int,
month int
)
insert into tb(id,data,month) values('001',11,1)
insert into tb(id,data,month) values('001',12,2)
insert into tb(id,data,month) values('001',13,3)
insert into tb(id,data,month) values('001',14,4)
insert into tb(id,data,month) values('001',15,5)
insert into tb(id,data,month) values('001',16,6)
insert into tb(id,data,month) values('001',17,7)
insert into tb(id,data,month) values('001',18,8)
insert into tb(id,data,month) values('001',19,9)
insert into tb(id,data,month) values('001',110,10)
insert into tb(id,data,month) values('001',111,11)
insert into tb(id,data,month) values('001',112,12)
insert into tb(id,data,month) values('002',21,1)
insert into tb(id,data,month) values('002',22,2)
insert into tb(id,data,month) values('002',23,3)
insert into tb(id,data,month) values('002',24,4)
insert into tb(id,data,month) values('002',25,5)
insert into tb(id,data,month) values('002',26,6)
insert into tb(id,data,month) values('002',27,7)
insert into tb(id,data,month) values('002',28,8)
insert into tb(id,data,month) values('002',29,9)
insert into tb(id,data,month) values('002',210,10)
insert into tb(id,data,month) values('002',211,11)
insert into tb(id,data,month) values('002',212,12)
goSELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月'
FROM tb
GROUP BY ID drop table tb/*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212(所影响的行数为 2 行)
*/--------------------------------------------------------------
/*
合计每个人每年的数据
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,
SUM(data) as '合计'
FROM tb
GROUP BY ID /*
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858
*/