关于分析函数（急求sql）
这个sql能用分析函数改写 或者优化不 太慢了
select SUM(DECODE(a.summary_acct_item_id, '100000 ', a.CHARGE, ' ')) CHARGE100000,
SUM(DECODE(a.summary_acct_item_id, '100001 ', a.CHARGE, ' ')) CHARGE100001,
SUM(DECODE(a.summary_acct_item_id, '100002 ', a.CHARGE, ' ')) CHARGE100002,
SUM(DECODE(a.summary_acct_item_id, '100003 ', a.CHARGE, ' ')) CHARGE100003,
SUM(DECODE(a.summary_acct_item_id, '100004 ', a.CHARGE, ' ')) CHARGE100004,
SUM(DECODE(a.summary_acct_item_id, '100005 ', a.CHARGE, ' ')) CHARGE100005,
SUM(DECODE(a.summary_acct_item_id, '100006 ', a.CHARGE, ' ')) CHARGE100006,
SUM(DECODE(a.summary_acct_item_id, '100007 ', a.CHARGE, ' ')) CHARGE100007,
SUM(DECODE(a.summary_acct_item_id, '100008 ', a.CHARGE, ' ')) CHARGE100008,
SUM(DECODE(a.summary_acct_item_id, '100009 ', a.CHARGE, ' ')) CHARGE100009,
SUM(DECODE(a.summary_acct_item_id, '100010 ', a.CHARGE, ' ')) CHARGE100010,
SUM(DECODE(a.summary_acct_item_id, '100011 ', a.CHARGE, ' ')) CHARGE100011,
SUM(DECODE(a.summary_acct_item_id, '100012 ', a.CHARGE, ' ')) CHARGE100012,
SUM(DECODE(a.summary_acct_item_id, '100013 ', a.CHARGE, ' ')) CHARGE100013,
SUM(DECODE(a.summary_acct_item_id, '100014 ', a.CHARGE, ' ')) CHARGE100014,
SUM(DECODE(a.summary_acct_item_id, '100015 ', a.CHARGE, ' ')) CHARGE100015,
SUM(DECODE(a.summary_acct_item_id, '100016 ', a.CHARGE, ' ')) CHARGE100016,
SUM(DECODE(a.summary_acct_item_id, '100017 ', a.CHARGE, ' ')) CHARGE100017,
SUM(DECODE(a.summary_acct_item_id, '100018 ', a.CHARGE, ' ')) CHARGE100018,
SUM(DECODE(a.summary_acct_item_id, '100019 ', a.CHARGE, ' ')) CHARGE100019,
SUM(DECODE(a.summary_acct_item_id, '100020 ', a.CHARGE, ' ')) CHARGE100020,
SUM(DECODE(a.summary_acct_item_id, '100021 ', a.CHARGE, ' ')) CHARGE100021,
SUM(DECODE(a.summary_acct_item_id, '100022 ', a.CHARGE, ' ')) CHARGE100022,
SUM(DECODE(a.summary_acct_item_id, '100023 ', a.CHARGE, ' ')) CHARGE100023,
SUM(DECODE(a.summary_acct_item_id, '100024 ', a.CHARGE, ' ')) CHARGE100024,
SUM(DECODE(a.summary_acct_item_id, '100025 ', a.CHARGE, ' ')) CHARGE100025,
SUM(DECODE(a.summary_acct_item_id, '100026 ', a.CHARGE, ' ')) CHARGE100026,
SUM(DECODE(a.summary_acct_item_id, '100027 ', a.CHARGE, ' ')) CHARGE100027,
SUM(DECODE(a.summary_acct_item_id, '100028 ', a.CHARGE, ' ')) CHARGE100028,
SUM(DECODE(a.summary_acct_item_id, '100032 ', a.CHARGE, ' ')) CHARGE100032,
SUM(DECODE(a.summary_acct_item_id, '100031 ', a.CHARGE, ' ')) CHARGE100031,
SUM(DECODE(a.summary_acct_item_id, '100033 ', a.CHARGE, ' ')) CHARGE100033,
a.STAFF_ID,
a.CUST_ID,
a.CUST_NAME,
a.billing_cycle_id,
a.CUST_ADDRESS_NAME
from (select * from area where area_id in (101)) b, acct_income_2005 a
where a.billing_Cycle_Id > = 10702
and a.billing_Cycle_Id <= 10707
and b.area_id = a.area_id
group by a.STAFF_ID,
a.cust_id,
a.cust_name,
a.billing_cycle_id,
a.cust_address_name
[解决办法]
首先在 summary_acct_item_id, billing_Cycle_Id 和area_id列上建立索引，且可以改为
Select .....
from area b, acct_income_2005 a
where b.area_id = a.area_id and a.area_id=101 and
a.billing_Cycle_Id > = 10702
and a.billing_Cycle_Id <= 10707
group by a.STAFF_ID,
a.cust_id,
a.cust_name,
a.billing_cycle_id,
a.cust_address_name
[解决办法]
优化同上，这种情况用分析函数解决不了问题 ...
[解决办法]
jf
