create or replace view g1401 as
select
t1.curr_term_value as 本期余额,
t2.curr_term_value as 占资本净额比例,
t3.curr_term_value as 正常类,
t4.curr_term_value as 不良类,
t5.curr_term_value as 关注类,
t6.curr_term_value as 可疑类,
t7.curr_term_value as 损失类,
t1.version_id,t1.status,t1.Col_Id,t1.Row_ID,
t1.cur_id, t1.data_range_id,t1.rept_year,t1.rept_month,org_id
from ((select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='F') t1
inner join
(select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='G') t2
on t1.version_id=t2.version_id and t1.data_range_id=t2.data_range_id and t1.cur_id=t2.cur_id
and t1.rept_year=t2.rept_year and t1.org_id=t2.org_id and t1.rept_month=t2.rept_monthinner join
(select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='H') t3
on t1.version_id=t3.version_id and t1.data_range_id=t3.data_range_id and t1.cur_id=t3.cur_id
and t1.rept_year=t3.rept_year and t1.org_id=t3.org_id and t1.rept_month=t3.rept_month
inner join
(select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='I') t4
on t1.version_id=t4.version_id and t1.data_range_id=t4.data_range_id and t1.cur_id=t4.cur_id
and t1.rept_year=t4.rept_year and t1.org_id=t4.org_id and t1.rept_month=t4.rept_month
inner join (select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='J') t5
on t1.version_id=t5.version_id and t1.data_range_id=t5.data_range_id and t1.cur_id=t5.cur_id
and t1.rept_year=t5.rept_year and t1.org_id=t5.org_id and t1.rept_month=t5.rept_month
inner join
(select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='K') t6
on t1.version_id=t6.version_id and t1.data_range_id=t6.data_range_id and t1.cur_id=t6.cur_id
and t1.rept_year=t6.rept_year and t1.org_id=t6.org_id and t1.rept_month=t6.rept_month
inner join
(select version_id,status,Col_Id,Row_ID,
cell_index,curr_term_value,cur_id, data_range_id,rept_year,rept_month,org_id from
v_Report_Data where template_id='G1401' and (row_id<=17 and row_id>=8) and col_id='L') t7
on t1.version_id=t7.version_id and t1.data_range_id=t7.data_range_id and t1.cur_id=t7.cur_id
and t1.rept_year=t7.rept_year and t1.org_id=t7.org_id and t1.rept_month=t7.rept_month);其实是个很简单的行转列,这个表中共有3500条数据,但是查询很慢,谁能帮忙给看看
试试这个
SELECT SUM(decode(t1.col_id, 'F', curr_term_value, 0)) AS 本期余额,
SUM(decode(t1.col_id, 'G', curr_term_value, 0)) AS 占资本净额比例,
SUM(decode(t1.col_id, 'H', curr_term_value, 0)) AS 正常类,
SUM(decode(t1.col_id, 'I', curr_term_value, 0)) AS 不良类,
SUM(decode(t1.col_id, 'J', curr_term_value, 0)) AS 关注类,
SUM(decode(t1.col_id, 'K', curr_term_value, 0)) AS 可疑类,
SUM(decode(t1.col_id, 'L', curr_term_value, 0)) AS 损失类,
t1.version_id,
t1.status,
t1.Col_Id,
t1.Row_ID,
t1.cur_id,
t1.data_range_id,
t1.rept_year,
t1.rept_month,
org_id
FROM v_Report_Data t1
WHERE template_id = 'G1401' AND
(row_id <= 17 AND row_id >= 8)
GROUP BY t1.version_id,
t1.status,
t1.Col_Id,
t1.Row_ID,
t1.cur_id,
t1.data_range_id,
t1.rept_year,
t1.rept_month;