SELECT B,sum(x_p)/sum(a_p),wm_concat(C||','||x_p/a_p) from (SELECT B,C,SUM(D) a_p,SUM(E) x_p from table1 GROUP BY B,C) GROUP BY B可以不
select t.省份,to_char(round(sum(t.x)/sum(t.a)*100,3),'999.99')|| '%' 累计闲置率, to_char(round(sum(decode(t.统计月份,'2013-01',t.X,0))/decode(sum(decode(t.统计月份,'2013-01',t.A,0)),0,1,sum(decode(t.统计月份,'2013-01',t.A,0)))*100,3),'999.99')|| '%' Jan2013, to_char(round(sum(decode(t.统计月份,'2013-02',t.X,0))/decode(sum(decode(t.统计月份,'2013-02',t.A,0)),0,1,sum(decode(t.统计月份,'2013-02',t.A,0)))*100,3),'999.99')|| '%' Feb2013, to_char(round(sum(decode(t.统计月份,'2013-03',t.X,0))/decode(sum(decode(t.统计月份,'2013-03',t.A,0)),0,1,sum(decode(t.统计月份,'2013-03',t.A,0)))*100,3),'999.99')|| '%' Mar2013, to_char(round(sum(decode(t.统计月份,'2013-04',t.X,0))/decode(sum(decode(t.统计月份,'2013-04',t.A,0)),0,1,sum(decode(t.统计月份,'2013-04',t.A,0)))*100,3),'999.99')|| '%' Apr2013, to_char(round(sum(decode(t.统计月份,'2013-05',t.X,0))/decode(sum(decode(t.统计月份,'2013-05',t.A,0)),0,1,sum(decode(t.统计月份,'2013-05',t.A,0)))*100,3),'999.99')|| '%' May2013, to_char(round(sum(decode(t.统计月份,'2013-06',t.X,0))/decode(sum(decode(t.统计月份,'2013-06',t.A,0)),0,1,sum(decode(t.统计月份,'2013-06',t.A,0)))*100,3),'999.99')|| '%' Jun2013, to_char(round(sum(decode(t.统计月份,'2013-07',t.X,0))/decode(sum(decode(t.统计月份,'2013-07',t.A,0)),0,1,sum(decode(t.统计月份,'2013-07',t.A,0)))*100,3),'999.99')|| '%' Jul2013, to_char(round(sum(decode(t.统计月份,'2013-08',t.X,0))/decode(sum(decode(t.统计月份,'2013-08',t.A,0)),0,1,sum(decode(t.统计月份,'2013-08',t.A,0)))*100,3),'999.99')|| '%' Aug2013, to_char(round(sum(decode(t.统计月份,'2013-09',t.X,0))/decode(sum(decode(t.统计月份,'2013-09',t.A,0)),0,1,sum(decode(t.统计月份,'2013-09',t.A,0)))*100,3),'999.99')|| '%' Sep2013, to_char(round(sum(decode(t.统计月份,'2013-10',t.X,0))/decode(sum(decode(t.统计月份,'2013-10',t.A,0)),0,1,sum(decode(t.统计月份,'2013-10',t.A,0)))*100,3),'999.99')|| '%' Oct2013, to_char(round(sum(decode(t.统计月份,'2013-11',t.X,0))/decode(sum(decode(t.统计月份,'2013-11',t.A,0)),0,1,sum(decode(t.统计月份,'2013-11',t.A,0)))*100,3),'999.99')|| '%' Nov2013, to_char(round(sum(decode(t.统计月份,'2013-12',t.X,0))/decode(sum(decode(t.统计月份,'2013-12',t.A,0)),0,1,sum(decode(t.统计月份,'2013-12',t.A,0)))*100,3),'999.99')|| '%' Dec2013 from (select 省份,sum(闲置人员数) x,sum(人员总数) a,统计月份 from table1 group by 省份,统计月份) t group by t.省份
select 省份, case when 判断月份字段=1月份 then 月份下面显示的字段信息 end 2013年1月, case when 判断月份字段=2月份 then 月份下面显示的字段信息 end 2013年2月, case when 判断月份字段=3月份 then 月份下面显示的字段信息 end 2013年3月, case when 判断月份字段=4月份 then 月份下面显示的字段信息 end 2013年4月, ... from table1 group by 省份
5楼的建议不错。 WITH t AS (SELECT 'GUIZHOU' province, '201303' stat_month, 31 total_member, 29 idle_member, 'd1' dept FROM dual UNION ALL SELECT 'GUIZHOU', '201303', 39,27,'d2' FROM dual UNION ALL SELECT 'GUIZHOU', '201304',2,2,'d1' from dual UNION ALL SELECT 'GUIZHOU', '201304',1,1,'d2' from dual UNION ALL SELECT 'GUIZHOU', '201305',4,3,'d1' from dual UNION ALL SELECT 'GUIZHOU', '201306',2,1,'d2' from dual UNION ALL SELECT 'GUANGXI', '201303',5,2,'d1' from dual UNION ALL SELECT 'GUANGDO', '201303',13,7,'d2' from dual UNION ALL SELECT 'GUANGDO', '201304',3,1,'d2' from dual UNION ALL SELECT 'GUANGDO', '201304',9,4,'d2' from dual UNION ALL SELECT 'GUANGDO', '201305',6,3,'d2' from dual) SELECT * FROM (SELECT Nvl(Province,'ALL_PROVINCE') Province ,Nvl(Stat_Month, 'TOTAL_RATE') Stat_Month ,SUM(Idle_Member) / SUM(Total_Member) Rate FROM t WHERE Stat_Month BETWEEN '201303' AND '201306' GROUP BY ROLLUP(Province,Stat_Month)) PIVOT (SUM(Rate) FOR Stat_Month IN ('TOTAL_RATE', '201303', '201304', '201305', '201306') ) WHERE Province <> 'ALL_PROVINCE'PROVINCE 'TOTAL_RATE' '201303' '201304' '201305' '201306' ------------ ------------ ---------- ---------- ---------- ---------- GUANGDO 0.4838709677 0.53846153 0.41666666 0.5 GUANGXI 0.4 0.4 GUIZHOU 0.7974683544 0.8 1 0.75 0.5
(SELECT B,C,SUM(D) a_p,SUM(E) x_p from table1 GROUP BY B,C) GROUP BY B可以不
select t.省份,to_char(round(sum(t.x)/sum(t.a)*100,3),'999.99')|| '%' 累计闲置率,
to_char(round(sum(decode(t.统计月份,'2013-01',t.X,0))/decode(sum(decode(t.统计月份,'2013-01',t.A,0)),0,1,sum(decode(t.统计月份,'2013-01',t.A,0)))*100,3),'999.99')|| '%' Jan2013,
to_char(round(sum(decode(t.统计月份,'2013-02',t.X,0))/decode(sum(decode(t.统计月份,'2013-02',t.A,0)),0,1,sum(decode(t.统计月份,'2013-02',t.A,0)))*100,3),'999.99')|| '%' Feb2013,
to_char(round(sum(decode(t.统计月份,'2013-03',t.X,0))/decode(sum(decode(t.统计月份,'2013-03',t.A,0)),0,1,sum(decode(t.统计月份,'2013-03',t.A,0)))*100,3),'999.99')|| '%' Mar2013,
to_char(round(sum(decode(t.统计月份,'2013-04',t.X,0))/decode(sum(decode(t.统计月份,'2013-04',t.A,0)),0,1,sum(decode(t.统计月份,'2013-04',t.A,0)))*100,3),'999.99')|| '%' Apr2013,
to_char(round(sum(decode(t.统计月份,'2013-05',t.X,0))/decode(sum(decode(t.统计月份,'2013-05',t.A,0)),0,1,sum(decode(t.统计月份,'2013-05',t.A,0)))*100,3),'999.99')|| '%' May2013,
to_char(round(sum(decode(t.统计月份,'2013-06',t.X,0))/decode(sum(decode(t.统计月份,'2013-06',t.A,0)),0,1,sum(decode(t.统计月份,'2013-06',t.A,0)))*100,3),'999.99')|| '%' Jun2013,
to_char(round(sum(decode(t.统计月份,'2013-07',t.X,0))/decode(sum(decode(t.统计月份,'2013-07',t.A,0)),0,1,sum(decode(t.统计月份,'2013-07',t.A,0)))*100,3),'999.99')|| '%' Jul2013,
to_char(round(sum(decode(t.统计月份,'2013-08',t.X,0))/decode(sum(decode(t.统计月份,'2013-08',t.A,0)),0,1,sum(decode(t.统计月份,'2013-08',t.A,0)))*100,3),'999.99')|| '%' Aug2013,
to_char(round(sum(decode(t.统计月份,'2013-09',t.X,0))/decode(sum(decode(t.统计月份,'2013-09',t.A,0)),0,1,sum(decode(t.统计月份,'2013-09',t.A,0)))*100,3),'999.99')|| '%' Sep2013,
to_char(round(sum(decode(t.统计月份,'2013-10',t.X,0))/decode(sum(decode(t.统计月份,'2013-10',t.A,0)),0,1,sum(decode(t.统计月份,'2013-10',t.A,0)))*100,3),'999.99')|| '%' Oct2013,
to_char(round(sum(decode(t.统计月份,'2013-11',t.X,0))/decode(sum(decode(t.统计月份,'2013-11',t.A,0)),0,1,sum(decode(t.统计月份,'2013-11',t.A,0)))*100,3),'999.99')|| '%' Nov2013,
to_char(round(sum(decode(t.统计月份,'2013-12',t.X,0))/decode(sum(decode(t.统计月份,'2013-12',t.A,0)),0,1,sum(decode(t.统计月份,'2013-12',t.A,0)))*100,3),'999.99')|| '%' Dec2013
from (select 省份,sum(闲置人员数) x,sum(人员总数) a,统计月份 from table1 group by 省份,统计月份) t group by t.省份
case when 判断月份字段=1月份 then 月份下面显示的字段信息 end 2013年1月,
case when 判断月份字段=2月份 then 月份下面显示的字段信息 end 2013年2月,
case when 判断月份字段=3月份 then 月份下面显示的字段信息 end 2013年3月,
case when 判断月份字段=4月份 then 月份下面显示的字段信息 end 2013年4月,
...
from table1
group by 省份
WITH t AS (SELECT 'GUIZHOU' province, '201303' stat_month, 31 total_member, 29 idle_member, 'd1' dept FROM dual UNION ALL
SELECT 'GUIZHOU', '201303', 39,27,'d2' FROM dual UNION ALL
SELECT 'GUIZHOU', '201304',2,2,'d1' from dual UNION ALL
SELECT 'GUIZHOU', '201304',1,1,'d2' from dual UNION ALL
SELECT 'GUIZHOU', '201305',4,3,'d1' from dual UNION ALL
SELECT 'GUIZHOU', '201306',2,1,'d2' from dual UNION ALL
SELECT 'GUANGXI', '201303',5,2,'d1' from dual UNION ALL
SELECT 'GUANGDO', '201303',13,7,'d2' from dual UNION ALL
SELECT 'GUANGDO', '201304',3,1,'d2' from dual UNION ALL
SELECT 'GUANGDO', '201304',9,4,'d2' from dual UNION ALL
SELECT 'GUANGDO', '201305',6,3,'d2' from dual)
SELECT *
FROM (SELECT Nvl(Province,'ALL_PROVINCE') Province
,Nvl(Stat_Month, 'TOTAL_RATE') Stat_Month
,SUM(Idle_Member) / SUM(Total_Member) Rate
FROM t
WHERE Stat_Month BETWEEN '201303' AND '201306'
GROUP BY ROLLUP(Province,Stat_Month))
PIVOT (SUM(Rate)
FOR Stat_Month
IN ('TOTAL_RATE', '201303', '201304', '201305', '201306')
)
WHERE Province <> 'ALL_PROVINCE'PROVINCE 'TOTAL_RATE' '201303' '201304' '201305' '201306'
------------ ------------ ---------- ---------- ---------- ----------
GUANGDO 0.4838709677 0.53846153 0.41666666 0.5
GUANGXI 0.4 0.4
GUIZHOU 0.7974683544 0.8 1 0.75 0.5