最终效果
数据库中表的一条记录中只有一个车型对应一个支付方式和车辆类型关联的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
得到下面的临时表:
这样,就出现了问题,如何将同一种车型记录整理成报表需要的样式,即为一种车型对应一条记录,这条记录中包含不同的车辆类型对应的支付方式,继续对上面临时表做查询,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
本次在项目种主要用到订单报表这一块
下面是根据订单和费用名称分组后的数据。
SELECT BIZ_DATE,
CNT_TYPE,
CTN_NO,
FREIGHT_BEGIN_DATE,
FREIGHT_END_DATE,
CUST_COMP_NO,
IS_SPOT,
CBM,
QUANTITY,
BC_LEDGER_COMP_ID,
BC_PUBLIC_ORDER_ID,
BIZ_SYSTEM_ORDER_ID,
CASE
WHEN freight_name_cn = '单证费' THEN '单证费'
WHEN freight_name_cn='进门费' THEN '进门费'
WHEN freight_name_cn='卸货费' THEN '卸货费'
WHEN freight_name_cn='紧固绳' THEN '紧固绳'
WHEN freight_name_cn='隔断板' THEN '隔断板'
WHEN freight_name_cn='隔断网' THEN '隔断网'
WHEN freight_name_cn='充气袋' THEN '充气袋'
WHEN freight_name_cn='堆存费' THEN '堆存费'
WHEN freight_name_cn='洋山提箱进洋山' OR freight_name_cn='外港提箱进洋山' OR freight_name_cn='洋山提箱进外港' OR freight_name_cn='外港提箱进外港' OR freight_name_cn='周边提箱进洋山' then '内装费'
ELSE '额外费用' END FREIGHT_NAME_CN,
SETTLE_AMOUNT from bc_freight where BC_LEDGER_COMP_ID = '6597360213400215552'
SELECT
BCFR.BIZ_DATE,
BCPO.VESSEL_NAME,
BCPO.VOYAGE,
BCPO.MBL_NO,
LEFT ( BCFR.CNT_TYPE, 2 ) AS LEFT_CNT_TYPE,
RIGHT ( BCFR.CNT_TYPE, 2 ) AS RIGHT_CNT_TYPE,
IF(BCPO.BIZ_TYPE = 'OUT',BCPO.CTN_NO,BCPO.JOB_NO) AS CTN_NO,
IF(BCPO.BIZ_TYPE='IN',BCPO.SETTLEMENT_BIZ_NO,'') AS SETTLEMENT_BIZ_JOB_NO,
BCPO .SETTLEMENT_BIZ_NO,
BCPO .JOB_NO,
BCFR.CUST_COMP_NO,
BCPO.CONSIGNOR_CODE,
BCPO.BIZ_UNITS_CODE,
BCFR.IS_SPOT,
BCFR.FREIGHT_BEGIN_DATE,
BCFR.FREIGHT_END_DATE,
SUM( BCFR.CBM ) AS TOTAL_CBM,
DATEDIFF( BCFR.FREIGHT_BEGIN_DATE, BCFR.FREIGHT_END_DATE ) AS DiffDate,
BCFR.QUANTITY,
BCFR.BC_LEDGER_COMP_ID,
BCFR.BC_PUBLIC_ORDER_ID,
BCFR.BIZ_SYSTEM_ORDER_ID,
FREIGHT_NAME_CN,
SUM(BCFR.SETTLE_AMOUNT) AMOUNT
FROM
(
SELECT BIZ_DATE,
CNT_TYPE,
CTN_NO,
FREIGHT_BEGIN_DATE,
FREIGHT_END_DATE,
CUST_COMP_NO,
IS_SPOT,
CBM,
QUANTITY,
BC_LEDGER_COMP_ID,
BC_PUBLIC_ORDER_ID,
BIZ_SYSTEM_ORDER_ID,
CASE
WHEN freight_name_cn = '单证费' THEN '单证费'
WHEN freight_name_cn='进门费' THEN '进门费'
WHEN freight_name_cn='卸货费' THEN '卸货费'
WHEN freight_name_cn='紧固绳' THEN '紧固绳'
WHEN freight_name_cn='隔断板' THEN '隔断板'
WHEN freight_name_cn='隔断网' THEN '隔断网'
WHEN freight_name_cn='充气袋' THEN '充气袋'
WHEN freight_name_cn='堆存费' THEN '堆存费'
WHEN freight_name_cn='洋山提箱进洋山' OR freight_name_cn='外港提箱进洋山' OR freight_name_cn='洋山提箱进外港' OR freight_name_cn='外港提箱进外港' OR freight_name_cn='周边提箱进洋山' then '内装费'
ELSE '额外费用' END FREIGHT_NAME_CN,
SETTLE_AMOUNT from bc_freight where BC_LEDGER_COMP_ID = '6597360213400215552'
) BCFR
JOIN BC_PUBLIC_ORDER BCPO ON BCPO.BC_PUBLIC_ORDER_ID = BCFR.BC_PUBLIC_ORDER_ID
GROUP BY BCFR.BC_PUBLIC_ORDER_ID, BCFR.FREIGHT_NAME_CN
目的统计各个订单下的各种费用
SELECT
BIZ_DATE,
VESSEL_NAME,
VOYAGE,
MBL_NO,
LEFT_CNT_TYPE,
RIGHT_CNT_TYPE,
CTN_NO,
SETTLEMENT_BIZ_JOB_NO,
SETTLEMENT_BIZ_NO,
JOB_NO,
CUST_COMP_NO,
CONSIGNOR_CODE,
BIZ_UNITS_CODE,
IS_SPOT,
TOTAL_CBM,
DiffDate
QUANTITY,
BC_LEDGER_COMP_ID,
BC_PUBLIC_ORDER_ID,
BIZ_SYSTEM_ORDER_ID,
FREIGHT_BEGIN_DATE,
FREIGHT_END_DATE,
SUM(if(FREIGHT_NAME_CN = '单证费',AMOUNT,0)) as DZ_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '进门费',AMOUNT,0)) as JM_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '卸货费',AMOUNT,0)) as XH_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '内装费',AMOUNT,0)) as NZ_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '紧固绳',AMOUNT,0)) as JGS_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '隔断板',AMOUNT,0)) as GDB_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '隔断网',AMOUNT,0)) as GDW_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '充气袋',AMOUNT,0)) as CQD_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '堆存费',AMOUNT,0)) as DC_AMOUNT,
SUM(if(FREIGHT_NAME_CN = '额外费用',AMOUNT,0)) as EW_AMOUNT
FROM
(
SELECT
BCFR.BIZ_DATE,
BCPO.VESSEL_NAME,
BCPO.VOYAGE,
BCPO.MBL_NO,
LEFT ( BCFR.CNT_TYPE, 2 ) AS LEFT_CNT_TYPE,
RIGHT ( BCFR.CNT_TYPE, 2 ) AS RIGHT_CNT_TYPE,
IF(BCPO.BIZ_TYPE = 'OUT',BCPO.CTN_NO,BCPO.JOB_NO) AS CTN_NO,
IF(BCPO.BIZ_TYPE='IN',BCPO.SETTLEMENT_BIZ_NO,'') AS SETTLEMENT_BIZ_JOB_NO,
BCPO .SETTLEMENT_BIZ_NO,
BCPO .JOB_NO,
BCFR.CUST_COMP_NO,
BCPO.CONSIGNOR_CODE,
BCPO.BIZ_UNITS_CODE,
BCFR.IS_SPOT,
BCFR.FREIGHT_BEGIN_DATE,
BCFR.FREIGHT_END_DATE,
SUM( BCFR.CBM ) AS TOTAL_CBM,
DATEDIFF( BCFR.FREIGHT_BEGIN_DATE, BCFR.FREIGHT_END_DATE ) AS DiffDate,
BCFR.QUANTITY,
BCFR.BC_LEDGER_COMP_ID,
BCFR.BC_PUBLIC_ORDER_ID,
BCFR.BIZ_SYSTEM_ORDER_ID,
FREIGHT_NAME_CN,
SUM(BCFR.SETTLE_AMOUNT) AMOUNT
FROM
(
SELECT BIZ_DATE,
CNT_TYPE,
CTN_NO,
FREIGHT_BEGIN_DATE,
FREIGHT_END_DATE,
CUST_COMP_NO,
IS_SPOT,
CBM,
QUANTITY,
BC_LEDGER_COMP_ID,
BC_PUBLIC_ORDER_ID,
BIZ_SYSTEM_ORDER_ID,
CASE
WHEN freight_name_cn = '单证费' THEN '单证费'
WHEN freight_name_cn='进门费' THEN '进门费'
WHEN freight_name_cn='卸货费' THEN '卸货费'
WHEN freight_name_cn='紧固绳' THEN '紧固绳'
WHEN freight_name_cn='隔断板' THEN '隔断板'
WHEN freight_name_cn='隔断网' THEN '隔断网'
WHEN freight_name_cn='充气袋' THEN '充气袋'
WHEN freight_name_cn='堆存费' THEN '堆存费'
WHEN freight_name_cn='洋山提箱进洋山' OR freight_name_cn='外港提箱进洋山' OR freight_name_cn='洋山提箱进外港' OR freight_name_cn='外港提箱进外港' OR freight_name_cn='周边提箱进洋山' then '内装费'
ELSE '额外费用' END FREIGHT_NAME_CN,
SETTLE_AMOUNT from bc_freight where BC_LEDGER_COMP_ID = '6597360213400215552'
) BCFR
JOIN BC_PUBLIC_ORDER BCPO ON BCPO.BC_PUBLIC_ORDER_ID = BCFR.BC_PUBLIC_ORDER_ID
GROUP BY BCFR.BC_PUBLIC_ORDER_ID, BCFR.FREIGHT_NAME_CN
) V_TEMP_1 GROUP BY BC_PUBLIC_ORDER_ID