把条件的顺序改下,把前面的 aa.ss_tmpl_id = 109 and aa.ss_id=a.ss_id and a.rowid = (select min(rowid) from ss where ss_id=a.ss_id) and a.row_num > 0 and a.row_num < 48 and b.col_num=0 and c.col_num=1 and d.col_num=2 and e.col_num=3 and f.col_num=4 放在最后,from后面把sa表写在最后,可能会好点,但是觉得你这个好像是要实现行列转换吧,应该可以写成别的形式,你拿点数据出来看看
ss.col_num是怎么存的?是否只有0,1,2,3,4? select ss_id,row_num,sum(decode(col_num,0,val,0)) as col0,sum(decode(col_num,1,val,0)) as col1,sum(decode(col_num,2,val,0)) as col2,sum(decode(col_num,3,val,0)) as col3,sum(decode(col_num,4,val,0)) as col4 from aa , ss where aa.ss_id=ss.ss_id and ss.rowid = (select min(rowid) from ss where ss_id=a.ss_id) and ss.row_num > 0 and ss.row_num < 48 and ss_tmpl_id = 109 看看是不是这个意思,再慢慢调
你做了6次ss表自身连接,而且ss表记录有20000000条,这样当然效率低啦,你试试用decode呢,不知道是不是你要的结果:select a.ss_id, a.row_num, decode(b.col_num,0,b.val) as col0, decode(b.col_num,1,b.val) as col1, decode(b.col_num,2,b.val) as col2, decode(b.col_num,3,b.val) as col3, decode(b.col_num,4,b.val) as col4 from sa aa, ss a where aa.ss_tmpl_id = 109 and aa.ss_id=a.ss_id and a.rowid = (select min(rowid) from ss where ss_id=a.ss_id) and a.row_num > 0 and a.row_num < 48
select a.ss_id,a.row_num,a.val as col0,a.val as col1,a.val as col2,a.val as col3,a.val as col4 from sa aa, (select * from ss where col_num in (0,1,2,3,4) and row_num > 0 and row_num < 48 and rowid = (select min(rowid) from ss ) )a where aa.ss_tmpl_id = 109 and aa.ss_id=a.ss_id
aa.ss_tmpl_id = 109
and aa.ss_id=a.ss_id
and a.rowid = (select min(rowid) from ss where ss_id=a.ss_id)
and a.row_num > 0 and a.row_num < 48
and b.col_num=0
and c.col_num=1
and d.col_num=2
and e.col_num=3
and f.col_num=4
放在最后,from后面把sa表写在最后,可能会好点,但是觉得你这个好像是要实现行列转换吧,应该可以写成别的形式,你拿点数据出来看看
select ss_id,row_num,sum(decode(col_num,0,val,0)) as col0,sum(decode(col_num,1,val,0)) as col1,sum(decode(col_num,2,val,0)) as col2,sum(decode(col_num,3,val,0)) as col3,sum(decode(col_num,4,val,0)) as col4
from aa , ss
where aa.ss_id=ss.ss_id
and ss.rowid = (select min(rowid) from ss where ss_id=a.ss_id)
and ss.row_num > 0 and ss.row_num < 48
and ss_tmpl_id = 109
看看是不是这个意思,再慢慢调
a.row_num,
decode(b.col_num,0,b.val) as col0,
decode(b.col_num,1,b.val) as col1,
decode(b.col_num,2,b.val) as col2,
decode(b.col_num,3,b.val) as col3,
decode(b.col_num,4,b.val) as col4
from sa aa, ss a
where aa.ss_tmpl_id = 109
and aa.ss_id=a.ss_id
and a.rowid = (select min(rowid) from ss where ss_id=a.ss_id)
and a.row_num > 0 and a.row_num < 48
from sa aa,
(select * from ss
where col_num in (0,1,2,3,4)
and row_num > 0 and row_num < 48
and rowid = (select min(rowid) from ss )
)a
where aa.ss_tmpl_id = 109
and aa.ss_id=a.ss_id