今天网友发来一个问题,描述如下:
有一个表(其实是他建的视图):
最终效果图如下:
建表语句如下:
-- Create table
create table DATAS
(
id NUMBER(38) not null,
modelid VARCHAR2(8) not null,
value VARCHAR2(2000),
areacode VARCHAR2(10) not null
)
数据请见附件
然后这位兄台自己建了个视图,就是一开始说的那个。
create view Vdatas as
select id,
case when substr(modelid,7,2)='38' then modelid||'R6_1'||value end as R6_1,
case when substr(modelid,7,2)='39' then modelid||'R6_2'||value end as R6_2,
case when substr(modelid,7,2)='40' then modelid||'R6_3'||value end as R6_3,
case when substr(modelid,7,2)='41' then modelid||'R6_4'||value end as R6_4,
case when substr(modelid,7,2)='43' then modelid||'R6_5'||value end as R6_5
from datas where substr(modelid,7,2) in ('38','39','40','41','43')
最一开始我用了很笨的办法:一个视图读五遍
select t1.id, t1.R6_1, t2.R6_2, t3.R6_3, t4.R6_4, t5.R6_5
from Vdatas t1, Vdatas t2,Vdatas t3, Vdatas t4, Vdatas t5
where substr(t1.R6_1, 1, 6) = substr(t2.R6_2, 1, 6)
and substr(t2.R6_2, 1, 6) = substr(t3.R6_3, 1, 6)
and substr(t3.R6_3, 1, 6) = substr(t4.R6_4, 1, 6)
and substr(t4.R6_4, 1, 6) = substr(t5.R6_5, 1, 6)
and t1.id = t2.id
and t2.id = t3.id
and t3.id = t4.id
and t4.id = t5.id
order by id
后来询问得知预计有2KW数据量,这么算下去5次扫描基本就要读2亿的数据。这个性能太慢,于是重新使用pivot函数实现了。
最后修改后的SQL如下:
with laotouzi as
(select a.id,
a.modelid,
a.value,
a.areacode,
substr(modelid, 7, 2) as str,
substr(modelid, 1, 6) as en,
case substr(modelid, 7, 2)
when '38' then
'R6_1'
when '39' then
'R6_2'
when '40' then
'R6_3'
when '41' then
'R6_4'
when '43' then
'R6_5'
end as title
from datas a
where substr(modelid, 7, 2) in ('38', '39', '40', '41', '43'))
select *
from laotouzi t pivot(max(case str
when '38' then
modelid || 'R6_1' || value
when '39' then
modelid || 'R6_2' || value
when '40' then
modelid || 'R6_3' || value
when '41' then
modelid || 'R6_4' || value
when '43' then
modelid || 'R6_5' || value
end) for title in('R6_1', 'R6_2', 'R6_3', 'R6_4', 'R6_5'));
关于Pivot函数可以参考附件的文档学习。