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