一个sql的四种写法:sum...case.when、sum if、select(select..)、group by

来源：互联网发布：论坛推广软件下载编辑：程序博客网时间：2024/06/06 07:43

500w数据

     SELECT
   sum(LOANEE_AMOUNT) as 'JE',
   sum(case when a.DEBT_TYPE = 0 then LOANEE_AMOUNT else 0 end) as 'CZ',
   sum(case when a.DEBT_TYPE = 1 then LOANEE_AMOUNT else 0 end) as 'EJZ'
        FROM
        t_zg_loanee_apply a
        WHERE
        DELETE_STATUS=0

耗时10s 只遍历一次。

select
sum( if(a.DEBT_TYPE = 0, LOANEE_AMOUNT, 0)) as 'CZ'  ,
sum( if(a.DEBT_TYPE = 1,LOANEE_AMOUNT, 0 )) as'EJZ'
        FROM
        t_zg_loanee_apply a
        WHERE
        DELETE_STATUS=0

一次遍历耗时9.9s

select (select SUM(LOANEE_AMOUNT) FROM   t_zg_loanee_apply a WHERE   DELETE_STATUS = 0 ) as 'JE',
       (select SUM(LOANEE_AMOUNT) FROM   t_zg_loanee_apply a WHERE   DELETE_STATUS = 0 and DEBT_TYPE = 0) as 'CZ',
       (select SUM(LOANEE_AMOUNT) FROM   t_zg_loanee_apply a WHERE   DELETE_STATUS = 0 and DEBT_TYPE = 1) as 'EJZ'
三次遍历，耗时30s

select DEBT_TYPE , count(*) , sum(LOANEE_AMOUNT)
from t_zg_loanee_apply
where DELETE_STATUS=0
group by DEBT_TYPE
两次遍历，耗时22s
对mysql压力未知。对性能要求不高且为了不分mysql大量资源。。。组长让我用简单sql。

ps：group by 等的使用技巧
http://www.cnblogs.com/allensun-193/p/5897925.html

阅读全文

0 0