优化一条SQL

lovebin_bin 2010-07-23 02:40:53

select (select g.game_name from game g where g.game_id = s.game_id) gameName,
nvl(sum(s.sales_count), 0) TotalSaleCount,
nvl(sum(s.sales_total), 0) TotalSales,
nvl(sum(s.m_sales_commission_amount), 0) TotalSalesCommission,
nvl(sum(s.payout_count), 0) PayoutAmount,
nvl(sum(s.payout_total), 0) TotalPayout,
nvl(sum(s.m_payout_commission_amount), 0) TotalPayoutCommission
from merchant_per s, mer m
where s.game_id is not null
and s.merchant_id = m.merchant_id
and m.parent_id = 1
group by s.game_id

11 rows selected in 18.023 seconds

select count(*) from merchant_per 返回：600000条记录
select count(*) from mer; 返回：3000条记录
select count(*) from game; 返回20条记录

数据库用的是：Oracle

...全文

140 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

lovebin_bin 2010-07-23

打赏
举报

谢谢OrchidCat了结贴

cheng20100915 2010-07-23

打赏
举报

cheng20100915 2010-07-23

打赏
举报

...........

lovebin_bin 2010-07-23

打赏
举报

to OrchidCat
谢谢，经过这一优化后，快多了

Mr_Nice 2010-07-23

打赏
举报

try Oracle的没弄过。试试看吧！

SELECT   g.game_name as gameName ,

        nvl(SUM(s.sales_count), 0) TotalSaleCount ,

        nvl(SUM(s.sales_total), 0) TotalSales ,

        nvl(SUM(s.m_sales_commission_amount), 0) TotalSalesCommission ,

        nvl(SUM(s.payout_count), 0) PayoutAmount ,

        nvl(SUM(s.payout_total), 0) TotalPayout ,

        nvl(SUM(s.m_payout_commission_amount), 0) TotalPayoutCommission

FROM    merchant_per s ,

        mer m,

        game g

WHERE   s.game_id IS NOT NULL

        AND s.merchant_id = m.merchant_id

        AND m.parent_id = 1

        AND g.game_id = s.game_id

GROUP BY g.game_name