StackTc 2018-07-05 09:31 采纳率: 90.9%
浏览 3009
已采纳

sql求解mysql中的max 跟case when联合

sql如下

MAX(CASE repaymentStatus WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,

这个max我感觉一点用没有 各位有用吗。

select sts.cusServerId,sts.name,sts.urgeTeamName,sum(sts.urgeNum) urgeNum,
        MAX(CASE Status WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,
        MAX(CASE Status WHEN '20' THEN urgeSuccessAmount ELSE 0 END ) urgeSuccessAmount,
        MAX(CASE repaymentStatus WHEN '20' THEN urgeNum ELSE 0 END )/sum(sts.urgeNum) urgeSuccessRate
        from (
        select  a.col_admin_id cusServerId,f.roleid urgeTeamName,a.col_admin_name name,count(1) urgeNum,
        a.repayment_status repaymentStatus,sum(a.already_repayment_amount)  urgeSuccessAmount
        from t_loan_apply_record a  left join t_uac_user f on a.col_admin_id = f.id
        where  exists (select 1 from t_loan_col_record b where a.id = b.apply_id)

        <if test='dateTo!=null and dateTo!=""'>
            AND a.release_time  <![CDATA[<= #{dateTo} ]]>
        </if>

        group by a.col_admin_id,repayment_status
        ) sts group by sts.cusServerId

  • 写回答

6条回答 默认 最新

  • gaoyuanhehe 2018-07-06 02:31
    关注

    是否有更优的解决效果不谈,就本身sql来讲,是有用的。
    MAX(CASE Status WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,
    理解为如果 Status为20,那么效果等同于MAX(urgeNum)结果为urgeNum的最大值,否则计算MAX(0),结果为0

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(5条)

报告相同问题?