需求是,求每个月份下,支付订单量、结算订单量、总订单量(各订单量的查询条件并不相同,但是返回数据格式相同)
想要的结果如图:
但是每类订单量的查询条件是不一样的,如下列各查询的条件 order_status_key
首先是要将每个月份下的各订单量分别查询出来:
-- 每月支付订单量
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,count(o.guide_order_id) payOrderCount,0 finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 1 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c') ;
-- 结果
| dateValue | payOrderCount | finishOrderCount | allOrderCount |
3月 28 0 0
4月 1591 0 0
5月 5282 0 0
6月 8097 0 0
7月 1260 0 0
-- 每月结算订单量
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,count(o.guide_order_id) finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 2 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c') ;
-- 结果
| dateValue | payOrderCount | finishOrderCount | allOrderCount |
2月 0 2 0
3月 0 159 0
4月 0 1454 0
5月 0 4209 0
6月 0 5676 0
7月 0 170 0
-- 每月总订单量
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,0 finishOrderCount,count(o.guide_order_id) allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key in (1,2) and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c');
-- 结果
| dateValue | payOrderCount | finishOrderCount | allOrderCount |
2月 0 0 2
3月 0 0 187
4月 0 0 3045
5月 0 0 9491
6月 0 0 13773
7月 0 0 1430
然后UNION合并这些结果,然而并不是想要的结果:
-- 使用UNION语句合并数据
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,count(o.guide_order_id) payOrderCount,0 finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 1 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c')
UNION
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,count(o.guide_order_id) finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 2 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c')
UNION
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,0 finishOrderCount,count(o.guide_order_id) allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key in (1,2) and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c');
-- 结果
| dateValue | payOrderCount | finishOrderCount | allOrderCount |
3月 28 0 0
4月 1591 0 0
5月 5282 0 0
6月 8097 0 0
7月 1260 0 0
2月 0 2 0
3月 0 159 0
4月 0 1454 0
5月 0 4209 0
6月 0 5676 0
7月 0 170 0
2月 0 0 2
3月 0 0 187
4月 0 0 3045
5月 0 0 9491
6月 0 0 13773
7月 0 0 1430
将上边的查询结果用临时表按日期字段分组查询,各订单求和汇总,得到想要的结果:
-- 用临时表解决问题
select dateValue,sum(payOrderCount) payOrderCount,sum(finishOrderCount) finishOrderCount,sum(allOrderCount) allOrderCount from
(
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,count(o.guide_order_id) payOrderCount,0 finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 1 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c')
UNION
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,count(o.guide_order_id) finishOrderCount,0 allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key = 2 and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c')
UNION
select CONCAT(DATE_FORMAT(o.pay_date,'%c'),'月') dateValue,0 payOrderCount,0 finishOrderCount,count(o.guide_order_id) allOrderCount from boom_shopping_guide_order o WHERE o.order_status_key in (1,2) and o.pay_date between "2019-01-01" and "2019-12-31" group by DATE_FORMAT(o.pay_date,'%c')
) tempTable group by dateValue;
-- 结果
| dateValue | payOrderCount | finishOrderCount | allOrderCount |
2月 0 2 2
3月 28 159 187
4月 1591 1454 3045
5月 5282 4209 9491
6月 8097 5676 13773
7月 1260 170 1430