spid spname opflag resultcod deviceid1 35100508 手机报联调测试 1 0 002302
2 35100508 手机报联调测试 1 0 002401
3 35100508 手机报联调测试 1 002500
4 35100508 手机报联调测试 1 001701
5 35100508 手机报联调测试 1 0 001801
6 35100508 手机报联调测试 1 000300
7 35100508 手机报联调测试 1 000400
8 35100508 手机报联调测试 1 001001
9 35100508 手机报联调测试 1 000701
10 35100508 手机报联调测试 1 000801
11 35100508 手机报联调测试 1 20 001100
12 35100508 手机报联调测试 1 002701
13 35100508 手机报联调测试 1 001501
14 35100508 手机报联调测试 1 001600
15 35100508 手机报联调测试 1 20 000200
16 35100508 手机报联调测试 1 003001
17 35100508 手机报联调测试 1 0 003101
18 35100508 手机报联调测试 1 001301
19 35100508 手机报联调测试 1 001900
20 35100508 手机报联调测试 1 001401
21 35100508 手机报联调测试 1 0 002600
22 35100508 手机报联调测试 1 20 001200
23 35100508 手机报联调测试 1 20 002801
24 35100508 手机报联调测试 1 0 000101
25 35100508 手机报联调测试 1 0 002201
26 35100508 手机报联调测试 1 0 002100
27 35100508 手机报联调测试 1 0 002901
28 35100508 手机报联调测试 1 000501
29 35100508 手机报联调测试 1 000600
30 35100508 手机报联调测试 1 000901
31 35100508 手机报联调测试 1 0 002001
上面数据的deviceid字段是设备编号,中间两位区分是哪一个省份,01代表北京......31代表新疆
我想要的结果:
spid spname opflag 北京的:resultcode 北京的:resultcode ..... 新疆的resultcode
35100508 手机报联调测试 1 0 20 ..... 0000不要使用子查询拼接,会挂掉的。肯请高手指教!
2 35100508 手机报联调测试 1 0 002401
3 35100508 手机报联调测试 1 002500
4 35100508 手机报联调测试 1 001701
5 35100508 手机报联调测试 1 0 001801
6 35100508 手机报联调测试 1 000300
7 35100508 手机报联调测试 1 000400
8 35100508 手机报联调测试 1 001001
9 35100508 手机报联调测试 1 000701
10 35100508 手机报联调测试 1 000801
11 35100508 手机报联调测试 1 20 001100
12 35100508 手机报联调测试 1 002701
13 35100508 手机报联调测试 1 001501
14 35100508 手机报联调测试 1 001600
15 35100508 手机报联调测试 1 20 000200
16 35100508 手机报联调测试 1 003001
17 35100508 手机报联调测试 1 0 003101
18 35100508 手机报联调测试 1 001301
19 35100508 手机报联调测试 1 001900
20 35100508 手机报联调测试 1 001401
21 35100508 手机报联调测试 1 0 002600
22 35100508 手机报联调测试 1 20 001200
23 35100508 手机报联调测试 1 20 002801
24 35100508 手机报联调测试 1 0 000101
25 35100508 手机报联调测试 1 0 002201
26 35100508 手机报联调测试 1 0 002100
27 35100508 手机报联调测试 1 0 002901
28 35100508 手机报联调测试 1 000501
29 35100508 手机报联调测试 1 000600
30 35100508 手机报联调测试 1 000901
31 35100508 手机报联调测试 1 0 002001
上面数据的deviceid字段是设备编号,中间两位区分是哪一个省份,01代表北京......31代表新疆
我想要的结果:
spid spname opflag 北京的:resultcode 北京的:resultcode ..... 新疆的resultcode
35100508 手机报联调测试 1 0 20 ..... 0000不要使用子查询拼接,会挂掉的。肯请高手指教!
sum(decode(substr(deviceid,3,2),'01',resultcod,0))"北京",
....
sum(decode(substr(deviceid,3,2),'31',resultcod,0))"新疆"
from TABLENAME
group by spid,spname,opflag
看到我的那条35100508的数据了吧,resultcode都在一列呢,我要把它们分开,靠deviceid来分开。
最终结果:
spid spname opflag resultcode1 ...... resultcode31
35100508 XXX 1 0 ...... 20
spv.spname,
con.opflag,
decode(substr(con.deviceid, 3, 2), '01', con.resultcode, -1) beijing,
decode(substr(con.deviceid, 3, 2), '02', con.resultcode, -1) shanghai,
decode(substr(con.deviceid, 3, 2), '03', con.resultcode, -1) tainjin,
decode(substr(con.deviceid, 3, 2), '04', con.resultcode, -1) chongqing,
decode(substr(con.deviceid, 3, 2), '05', con.resultcode, -1) heibei,
decode(substr(con.deviceid, 3, 2), '06', con.resultcode, -1) shanxi,
decode(substr(con.deviceid, 3, 2), '07', con.resultcode, -1) neimeng,
decode(substr(con.deviceid, 3, 2), '08', con.resultcode, -1) liaoning,
decode(substr(con.deviceid, 3, 2), '09', con.resultcode, -1) jilin,
decode(substr(con.deviceid, 3, 2), '10', con.resultcode, -1) heilongjiang,
decode(substr(con.deviceid, 3, 2), '11', con.resultcode, -1) jiangsu,
decode(substr(con.deviceid, 3, 2), '12', con.resultcode, -1) zhejiang,
decode(substr(con.deviceid, 3, 2), '13', con.resultcode, -1) anhui,
decode(substr(con.deviceid, 3, 2), '14', con.resultcode, -1) fujian,
decode(substr(con.deviceid, 3, 2), '15', con.resultcode, -1) jiangxi,
decode(substr(con.deviceid, 3, 2), '16', con.resultcode, -1) shandong,
decode(substr(con.deviceid, 3, 2), '17', con.resultcode, -1) henan,
decode(substr(con.deviceid, 3, 2), '18', con.resultcode, -1) hubei,
decode(substr(con.deviceid, 3, 2), '19', con.resultcode, -1) hunan,
decode(substr(con.deviceid, 3, 2), '20', con.resultcode, -1) guangdong,
decode(substr(con.deviceid, 3, 2), '21', con.resultcode, -1) guangxi,
decode(substr(con.deviceid, 3, 2), '22', con.resultcode, -1) hainan,
decode(substr(con.deviceid, 3, 2), '23', con.resultcode, -1) sichuan,
decode(substr(con.deviceid, 3, 2), '24', con.resultcode, -1) guizhou,
decode(substr(con.deviceid, 3, 2), '25', con.resultcode, -1) yunnan,
decode(substr(con.deviceid, 3, 2), '26', con.resultcode, -1) xizang,
decode(substr(con.deviceid, 3, 2), '27', con.resultcode, -1) shanxi,
decode(substr(con.deviceid, 3, 2), '28', con.resultcode, -1) gansu,
decode(substr(con.deviceid, 3, 2), '29', con.resultcode, -1) qinghai,
decode(substr(con.deviceid, 3, 2), '30', con.resultcode, -1) ningxia,
decode(substr(con.deviceid, 3, 2), '31', con.resultcode, -1) xinjiang,
con.deviceid
from spinfotabrpt sp, configinforpt con, spinfovisual_zhrpt spv
where spv.spid = con.dataid
and con.dataid = sp.spid
and con.datatype = 1
and con.devicetype = 11
and sp.spid = '35100508'
group by sp.spid, spv.spname, con.opflag, con.resultcode, con.deviceid
order by sp.spid处理到这一步显示结果:2 35100508 中国电信手机报联调测试 1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 001501
3 35100508 中国电信手机报联调测试 1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 20 -1 -1 -1 002801
4 35100508 中国电信手机报联调测试 1 0 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 000101
5 35100508 中国电信手机报联调测试 1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 000801
6 35100508 中国电信手机报联调测试 1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 002701
7 35100508 中国电信手机报联调测试 1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 001600
怎么样把它合到一行去?
在结果上再根据条件sum、group by一下么
可以考虑下建个物化视图吧。物化视图,仅做参考:
http://blog.csdn.net/suncrafted/archive/2009/06/26/4300358.aspx
http://topic.csdn.net/u/20091225/14/e225f4f6-03f6-4684-b82b-45d58e045ad5.html?seed=2012731555&r=62218282#r_62218282