看下面的表结构:
Category | 设备类型 | String |
DeviceNum | 设备数量 | Number |
Area | 区域 | String |
查询结果如下:
现在要显示成下图的效果就要使用普通的行转列语句。
sql语句:
1
select
AREA,
2 sum (decode(category, ' 2G基站 ' ,devicenum, null )) "2G",
3 sum (decode(category, ' 3G基站 ' ,devicenum, null )) "3G",
4 sum (decode(category, ' 总计 ' ,devicenum, null )) " ALL "
5 from MV_CUTOVER_BASESITE_CAPACITY
6 group by AREA
2 sum (decode(category, ' 2G基站 ' ,devicenum, null )) "2G",
3 sum (decode(category, ' 3G基站 ' ,devicenum, null )) "3G",
4 sum (decode(category, ' 总计 ' ,devicenum, null )) " ALL "
5 from MV_CUTOVER_BASESITE_CAPACITY
6 group by AREA
查询结果为:
上面的行转列比较简单,再看下面的表结构:
查询结果为:
现在需要达到下图这种效果:
这里的普通行转列不能完成,需要union all来连接:
1
select
'
已办工单
'
"TYPE", a.
*
from
(
2 select
3 sum (nvl(decode(area, ' 杭州 ' ,inprogressnum, null ), 0 )) "HANGZHOU" ,
4 sum (nvl(decode(area, ' 湖州 ' ,inprogressnum, null ), 0 )) "HUZHOU" ,
5 sum (nvl(decode(area, ' 金华 ' ,inprogressnum, null ), 0 )) "JINHUA" ,
6 sum (nvl(decode(area, ' 宁波 ' ,inprogressnum, null ), 0 )) "NINGBO" ,
7 sum (nvl(decode(area, ' 全省 ' ,inprogressnum, null ), 0 )) " ALL "
8 from mv_service_order
9 ) a, dual b
10 union all
11 select ' 超时工单 ' "TYPE", a. * from (
12 select
13 sum (nvl(decode(area, ' 杭州 ' ,achivenum, null ), 0 )) "HANGZHOU" ,
14 sum (nvl(decode(area, ' 湖州 ' ,achivenum, null ), 0 )) "HUZHOU" ,
15 sum (nvl(decode(area, ' 金华 ' ,achivenum, null ), 0 )) "JINHUA" ,
16 sum (nvl(decode(area, ' 宁波 ' ,achivenum, null ), 0 )) "NINGBO" ,
17 sum (nvl(decode(area, ' 全省 ' ,achivenum, null ), 0 )) " ALL "
18 from mv_service_order
19 )a, dual b
20 union all
21 select ' 已归档工单 ' "TYPE",a. * from (
22 select
23 sum (nvl(decode(area, ' 杭州 ' ,delaynum, null ), 0 )) "HANGZHOU" ,
24 sum (nvl(decode(area, ' 湖州 ' ,delaynum, null ), 0 )) "HUZHOU" ,
25 sum (nvl(decode(area, ' 金华 ' ,delaynum, null ), 0 )) "JINHUA" ,
26 sum (nvl(decode(area, ' 宁波 ' ,delaynum, null ), 0 )) "NINGBO" ,
27 sum (nvl(decode(area, ' 全省 ' ,delaynum, null ), 0 )) " ALL "
28 from mv_service_order
29 )a,dual b
30 union all
31 select ' 工单总量 ' "TYPE",a. * from (
32 select
33 sum (nvl(decode(area, ' 杭州 ' ,totalnum, null ), 0 )) "HANGZHOU" ,
34 sum (nvl(decode(area, ' 湖州 ' ,totalnum, null ), 0 )) "HUZHOU" ,
35 sum (nvl(decode(area, ' 金华 ' ,totalnum, null ), 0 )) "JINHUA" ,
36 sum (nvl(decode(area, ' 宁波 ' ,totalnum, null ), 0 )) "NINGBO" ,
37 sum (nvl(decode(area, ' 全省 ' ,totalnum, null ), 0 )) " ALL "
38 from mv_service_order
39 )a, dual b
2 select
3 sum (nvl(decode(area, ' 杭州 ' ,inprogressnum, null ), 0 )) "HANGZHOU" ,
4 sum (nvl(decode(area, ' 湖州 ' ,inprogressnum, null ), 0 )) "HUZHOU" ,
5 sum (nvl(decode(area, ' 金华 ' ,inprogressnum, null ), 0 )) "JINHUA" ,
6 sum (nvl(decode(area, ' 宁波 ' ,inprogressnum, null ), 0 )) "NINGBO" ,
7 sum (nvl(decode(area, ' 全省 ' ,inprogressnum, null ), 0 )) " ALL "
8 from mv_service_order
9 ) a, dual b
10 union all
11 select ' 超时工单 ' "TYPE", a. * from (
12 select
13 sum (nvl(decode(area, ' 杭州 ' ,achivenum, null ), 0 )) "HANGZHOU" ,
14 sum (nvl(decode(area, ' 湖州 ' ,achivenum, null ), 0 )) "HUZHOU" ,
15 sum (nvl(decode(area, ' 金华 ' ,achivenum, null ), 0 )) "JINHUA" ,
16 sum (nvl(decode(area, ' 宁波 ' ,achivenum, null ), 0 )) "NINGBO" ,
17 sum (nvl(decode(area, ' 全省 ' ,achivenum, null ), 0 )) " ALL "
18 from mv_service_order
19 )a, dual b
20 union all
21 select ' 已归档工单 ' "TYPE",a. * from (
22 select
23 sum (nvl(decode(area, ' 杭州 ' ,delaynum, null ), 0 )) "HANGZHOU" ,
24 sum (nvl(decode(area, ' 湖州 ' ,delaynum, null ), 0 )) "HUZHOU" ,
25 sum (nvl(decode(area, ' 金华 ' ,delaynum, null ), 0 )) "JINHUA" ,
26 sum (nvl(decode(area, ' 宁波 ' ,delaynum, null ), 0 )) "NINGBO" ,
27 sum (nvl(decode(area, ' 全省 ' ,delaynum, null ), 0 )) " ALL "
28 from mv_service_order
29 )a,dual b
30 union all
31 select ' 工单总量 ' "TYPE",a. * from (
32 select
33 sum (nvl(decode(area, ' 杭州 ' ,totalnum, null ), 0 )) "HANGZHOU" ,
34 sum (nvl(decode(area, ' 湖州 ' ,totalnum, null ), 0 )) "HUZHOU" ,
35 sum (nvl(decode(area, ' 金华 ' ,totalnum, null ), 0 )) "JINHUA" ,
36 sum (nvl(decode(area, ' 宁波 ' ,totalnum, null ), 0 )) "NINGBO" ,
37 sum (nvl(decode(area, ' 全省 ' ,totalnum, null ), 0 )) " ALL "
38 from mv_service_order
39 )a, dual b
查询结果如下: