关于分析函数（急求sql）

[hdevil]

这个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

[WangZWang]

首先在 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

[mantisXF]

优化同上，这种情况用分析函数解决不了问题 ...

[summersure]

jf