mysql查询时两个字段分组查询后依据一个字段行转列做报表样式

女爷i 2022-05-20 03:13 204阅读 0赞

第一次写博客，直接上干货，在工作时遇到要做一个报表，数据时从一个表中的不同车辆类型和不同的支付方式统计，要求样式如下：![70][]

数据库中表的一条记录中只有一个车型对应一个支付方式和车辆类型关联的id。主要报表字段数据表结构如下：

-- CREATE TABLE `parking_user_order` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `logistics_park_code` varchar(4) NOT NULL COMMENT '物流园编码',
      `appuser_id` int(11) NOT NULL COMMENT '会员id',
      `order_no` varchar(20) NOT NULL COMMENT '订单号',
      `user_type` smallint(6) DEFAULT NULL COMMENT '用户类型：0普通用户；1月保用户;2贵宾用户;3一卡通用户；4余额用户；5无感支付用户',
      `cartype_id` int(11) DEFAULT NULL COMMENT '车型id',
      `parking_peirod` varchar(20) DEFAULT NULL COMMENT '停车时长',
      `pay_channel` smallint(6) DEFAULT NULL COMMENT '支付渠道:0扫码；1月保；2出口亭收费',
      `pay_way` varchar(10) DEFAULT NULL COMMENT '支付方式：acct余额；wx微信支付；cash现金；wtk物通卡支付；ykt一卡通支付；upay无感支付',
      `pay_price` decimal(10,2) DEFAULT NULL COMMENT '支付价格',
      `status` smallint(1) DEFAULT NULL COMMENT '状态：0入园待支付；1出园已支付；2订单已失效',
      `create_time` datetime DEFAULT NULL COMMENT '入园时间',
      `out_time` datetime DEFAULT NULL COMMENT '出园时间',
      `pay_time` datetime DEFAULT NULL COMMENT '支付交易时间',
      `car_num` varchar(10) DEFAULT NULL COMMENT '车牌号',
      `receivable_price` decimal(10,2) DEFAULT NULL COMMENT '实收金额',
      `update_user_id` int(11) DEFAULT NULL COMMENT '操作员id',
      `park_area_id` int(11) DEFAULT NULL COMMENT '车场ID',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=569947 DEFAULT CHARSET=utf8mb4 COMMENT='停车用户订单表';

首先使用下面的sql先分组，得到相同车型的多行不同支付方式的记录。

SELECT
    		p.cartype_id carTypeId,
    		case when c.name is  null then '无车型' else c.name end as carTypeName,
    		case when (p.pay_way is null or p.pay_way = '' or p.pay_way = 'null') then 'other' else p.pay_way end as payWay ,
    		ifnull(sum(ifnull(p.pay_price, 0)),0)  as payPrice,
    		ifnull(sum(ifnull(p.receivable_price, 0)),0) as receivablePrice,
    		ifnull(sum(ifnull(p.coupon_amount, 0)),0) as coupon,
    		max(p.pay_time) AS outTime, 
    		count(1) as  countNum
    	FROM
    		parking_user_order p LEFT JOIN parking_cartype_config c on p.cartype_id = c.id
    	WHERE
    		p.logistics_park_code = '1000'
    	 and p.pay_time > '2018-07-09 00:00:00' and p.pay_time < '2018-07-10 00:00:00'
    	GROUP BY
    		c.name,
    		p.pay_way

得到下面的临时表：

![70 1][]

这样，就出现了问题，如何将同一种车型记录整理成报表需要的样式，即为一种车型对应一条记录，这条记录中包含不同的车辆类型对应的支付方式，继续对上面临时表做查询，sql如下：

\#\# 其他几个关联的表主要是关联查询车辆类型name和用户名称、车场名称，就不贴出来了。

SELECT base.carTypeName,
    sum(if(base.payWay = 'cash',base.payPrice,0)) as 'pay_cash',sum(if(base.payWay = 'cash',base.receivablePrice,0)) as 'rec_cash',
    sum(if(base.payWay = 'wtk',base.payPrice,0)) as 'pay_wtk',sum(if(base.payWay = 'wtk',base.receivablePrice,0)) as 'rec_wtk',
    sum(if(base.payWay = 'ykt',base.payPrice,0)) as 'pay_ykt',sum(if(base.payWay = 'ykt',base.receivablePrice,0)) as 'rec_ykt',
    sum(if(base.payWay = 'wx',base.payPrice,0)) as 'pay_wx',sum(if(base.payWay = 'wx',base.receivablePrice,0)) as 'rec_wx',
    sum(if(base.payWay = 'upay',base.payPrice,0)) as 'pay_upay',sum(if(base.payWay = 'upay',base.receivablePrice,0)) as 'rec_upay',
    sum(if(base.payWay = 'other',base.payPrice,0)) as 'pay_other',sum(if(base.payWay = 'other',base.receivablePrice,0)) as 'rec_other',
    sum(base.coupon) as coupon,
    sum(if(base.payWay = 'cash',base.payPrice,0)+ if(base.payWay = 'wtk',base.payPrice,0)) as 'pay',
    sum(if(base.payWay = 'cash',base.receivablePrice,0)+ if(base.payWay = 'wtk',base.receivablePrice,0)) as 'rec',
    sum(base.countNum) as count,
    date_format(max(base.outTime),'%Y-%m-%d %H:%i:%s')  AS 'outTime'
    from (
    SELECT
    p.cartype_id as carTypeId,
    case when c.name is  null then '无车型' else c.name end as carTypeName,
    case when (p.pay_way is null or p.pay_way = '' or p.pay_way = 'null') then 'other' else p.pay_way end as payWay ,
    ifnull(sum(ifnull(p.pay_price, 0)),0)  as payPrice,
    ifnull(sum(ifnull(p.receivable_price, 0)),0) as receivablePrice,
    ifnull(sum(ifnull(p.coupon_amount, 0)),0) as coupon,
    max(p.pay_time) AS outTime, 
    count(1) as  countNum
    FROM
    parking_user_order p LEFT JOIN parking_cartype_config c on p.cartype_id = c.id
    WHERE
    p.logistics_park_code = '1000'
    and p.status = 1 
    and p.pay_time > '2018-07-09 00:00:00' and p.pay_time < '2018-07-10 00:00:00'
    GROUP BY
    c.name,
    p.cartype_id,
    p.pay_way
    ) as  base GROUP BY base.carTypeName

当然，皮一点可以直接查询出这个报表一样的样式数据：

SELECT base.carTypeName as '车辆类型',
    sum(if(base.payWay = 'cash',base.payPrice,0)) as '现金应收',sum(if(base.payWay = 'cash',base.receivablePrice,0)) as '现金实收',
    sum(if(base.payWay = 'wtk',base.payPrice,0)) as '物通卡应收',sum(if(base.payWay = 'wtk',base.receivablePrice,0)) as '物通卡实收',
    sum(if(base.payWay = 'ykt',base.payPrice,0)) as '一卡通应收',sum(if(base.payWay = 'ykt',base.receivablePrice,0)) as '一卡通实收',
    sum(if(base.payWay = 'wx',base.payPrice,0)) as '微信应收',sum(if(base.payWay = 'wx',base.receivablePrice,0)) as '微信实收',
    sum(if(base.payWay = 'upay',base.payPrice,0)) as '无感支付应收',sum(if(base.payWay = 'upay',base.receivablePrice,0)) as '无感支付实收',
    sum(if(base.payWay = 'other',base.payPrice,0)) as '其他应收',sum(if(base.payWay = 'other',base.receivablePrice,0)) as '其他实收',
    sum(base.coupon) as '优惠卷',
    sum(if(base.payWay = 'cash',base.payPrice,0)+ if(base.payWay = 'wtk',base.payPrice,0)) as '应收',
    sum(if(base.payWay = 'cash',base.receivablePrice,0)+ if(base.payWay = 'wtk',base.receivablePrice,0)) as '实收',
    sum(base.countNum) as '收费次数',
    date_format(max(base.outTime),'%Y-%m-%d %H:%i:%s')  AS '出场时间'
    from (
    SELECT
    p.cartype_id carTypeId,
    case when c.name is  null then '无车型' else c.name end as carTypeName,
    case when (p.pay_way is null or p.pay_way = '' or p.pay_way = 'null') then 'other' else p.pay_way end as payWay ,
    ifnull(sum(ifnull(p.pay_price, 0)),0)  as payPrice,
    ifnull(sum(ifnull(p.receivable_price, 0)),0) as receivablePrice,
    ifnull(sum(ifnull(p.coupon_amount, 0)),0) as coupon,
    max(p.pay_time) AS outTime, 
    count(1) as  countNum
    FROM
    parking_user_order p LEFT JOIN parking_cartype_config c on p.cartype_id = c.id
    WHERE
    p.logistics_park_code = '1000'
    and p.pay_time > '2018-06-05 00:00:00' and p.pay_time < '2018-07-10 00:00:00'
    and p.status = 1
    GROUP BY
    c.name,
    p.cartype_id,
    p.pay_way
    
    ) as  base GROUP BY base.carTypeName

这样，就完成了通过sql语句对于行转列的转换实现效果：

![70 2][]

[70]: /images/20220520/98f6c741440847d9b001014116e329bb.png
[70 1]: /images/20220520/59e1f41e9808414c86ef6df15886cf89.png
[70 2]: /images/20220520/a8e7dc7a8f15492685f4c4b5153f6f95.png