mysql 行转列分类统计数量

    xiaoxiao2021-03-25  106

    mysql 行转列分类统计数量

    今天遇到的问题,以下流水表,根据请求渠道和交易类型,统计交易的成功、失败和处理中笔数

    数据库表:交易流水表(简化)

    流水号请求渠道状态(00:成功 01:失败 02:处理中)交易类型N00010100A0N00020200A1N00030101A1N00040200A0N00050201A0N00060201A1

    统计结果如下:

    请求渠道交易类型成功笔数失败笔数处理中笔数01A010001A101002A011002A1110

    实现思路: 1、先根据渠道和状态统计条数 2、使用行转列函数进行数据转换

    select b.channelid as '渠道ID',b.transtype as '交易类型', MAX(CASE b.status WHEN '00' THEN b.count ELSE 0 END) as '成功笔数', MAX(CASE b.status WHEN '01' THEN b.count ELSE 0 END) as '失败笔数', MAX(CASE b.status WHEN '02' THEN b.count ELSE 0 END) as '处理中笔数' from (SELECT a.channelid,a.transtype,a.status,count(*) as count from trans_flow a GROUP BY a.channelid,a.transtype,a.`status`) as b GROUP BY b.channelid,b.transtype
    转载请注明原文地址: https://ju.6miu.com/read-9522.html

    最新回复(0)