表名 xxta_account_f
表结构
account,number,date
101,1000,31-DEC-07
101,2000,30-NOV-07
102,200,31-DEC-07
102,400,30-NOV-07
103,100,31-DEC-07
103,800,30-NOV-07
104,2100,31-DEC-07
104,4100,30-NOV-07
要求是按account条件组合,取出当月数据,上月数据,年初数据,去年同期数据进行比较
要求报表结构
gather_account|当月数据|上月数据|年初数据|去年同期数据|(一些比较,用discover上工具做了)
短期(101) |1000 |2000 | xxxx |xxxxxx |
长期(102+103) |300 |1200 | xxxx |xxxxxx |
中期(104) |2100 |4100 | xxxx |xxxxxx |
我写的sql
select '短期' gather_account,a1.number 当月数据,a2.number 上月数据,a3.number 年初数据,a4.number 去年同期数据
from xxta_account_f_a a1,xxta_account_f_a a2,xxta_account_f_a a3,xxta_account_f_a a4
where add_months(a1.date,-1)=a2.date
and extract(year from a1.date)-1||1231 = to_char(a3.date,'yyyymmdd')
and add_months(a1.date,-12)=a4.date(+)
union
select '长期' gather_account,b1.number 当月数据,b2.number 上月数据,b3.number 年初数据,b4.number 去年同期数据
from xxta_account_f_b b1,xxta_account_f_b b2,xxta_account_f_b b3,xxta_account_f_b b4
where add_months(b1.date,-1)=b2.date
and extract(year from b1.date)-1||1231 = to_char(b3.date,'yyyymmdd')
and add_months(b1.date,-12)=b4.date(+)
union
select '中期' gather_account,c1.number 当月数据,c2.number 上月数据,c3.number 年初数据,c4.number 去年同期数据
from xxta_account_f_c c1,xxta_account_f_c c2,xxta_account_f_c c3,xxta_account_f_c c4
where add_months(c1.date,-1)=c2.date
and extract(year from c1.date)-1||1231 = to_char(c3.date,'yyyymmdd')
and add_months(c1.date,-12)=a4.date(+)
--------------------------------
其中xxta_account_f_a
select sum(number),date from xxta_account_f_a where account=101 group by date
其中xxta_account_f_b
select sum(number),date from xxta_account_f_a where account in (102,103) group by date
其中xxta_account_f_c
select sum(number),date from xxta_account_f_a where account=104 group by date
----------------------------------
请大家看看能不能改的简单一点,总感觉这个语句运行的颇慢.