Mysql行列转换sql语句

原创

a772304419 2021-07-02 13:50:17 ©著作权

文章标签 MySQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者a772304419的原创作品，请联系作者获取转载授权，否则将追究法律责任

@Select("<script>" +
            "select d.id driverId,d.name driverName,d.Org_Id orgId,d.cooperation_company_id cooperationCompanyId,\n" +
            "sum(CASE r.record_type WHEN 1 THEN r.record_money ELSE 0 END ) orderMoney,\n" +
            "sum(CASE r.record_type WHEN 2 THEN r.record_money ELSE 0 END ) withdrawalsMoney,\n" +
            "sum(CASE r.record_type WHEN 3 THEN r.record_money ELSE 0 END ) rewardMoney,\n" +
            "sum(CASE r.record_type WHEN 4 THEN r.record_money ELSE 0 END ) punishmentMoney,\n" +
            "sum(CASE r.record_type WHEN 5 THEN r.record_money ELSE 0 END ) shareMoney\n" +
            "from basic_driver d, driver_account_record r, (SELECT getChildrenOrg(#{orgId}) orgs) t\n" +
            "where FIND_IN_SET(d.org_Id, orgs)\n" +
            "and d.id=r.driver_id\n" +
            "<if test=\"driverName!=null and driverName!=''\">and d.name=#{driverName}</if>\n" +
            "<if test=\"startDate!=null and startDate!=''\"> and r.record_time  <![CDATA[ >= ]]> #{startDate} </if>\n" +
            "<if test=\"endDate!=null and endDate!=''\"> and r.record_time <![CDATA[ < ]]> date_add(#{endDate}, interval 1 day) </if>\n" +
            "<if test=\"cooperationCompanyId!=0 and cooperationCompanyId!=-1\">and d.cooperation_company_id=#{cooperationCompanyId}</if>\n" +
            "group by d.id" +
            "</script>")
List<AccountTotal> accountMoney(AccountTotal accountTotal);