这个sql有点东西，记录一下

我有一个需求：在订单表里面查询指定时间的订单数据，如果要是没有订单的话，需要展示当天日期和数据，数据为0

先看一下效果：

话不多说，直接上SQL

SELECT
	date_range.date AS 日期,
	COUNT( oco.id ) AS 总订单,
	COUNT( oco.pay_price ) AS 成功订单,
	IFNULL( sum( oco.pay_price ), 0 ) AS 客户付款金额 
FROM
	(
SELECT
	DATE( '2023-11-01' ) + INTERVAL t0.num DAY AS date 
FROM
	(
SELECT
	( a.num + b.num + c.num + d.num + e.num ) num 
FROM
	(
SELECT
	0 num UNION ALL
SELECT
	1 num UNION ALL
SELECT
	2 num UNION ALL
SELECT
	3 num UNION ALL
SELECT
	4 num UNION ALL
SELECT
	5 num UNION ALL
SELECT
	6 num UNION ALL
SELECT
	7 num UNION ALL
SELECT
	8 num UNION ALL
SELECT
	9 num 
	) a,
	(
SELECT
	0 num UNION ALL
SELECT
	10 num UNION ALL
SELECT
	20 num UNION ALL
SELECT
	30 num UNION ALL
SELECT
	40 num UNION ALL
SELECT
	50 num UNION ALL
SELECT
	60 num UNION ALL
SELECT
	70 num UNION ALL
SELECT
	80 num UNION ALL
SELECT
	90 num 
	) b,
	(
SELECT
	0 num UNION ALL
SELECT
	100 num UNION ALL
SELECT
	200 num UNION ALL
SELECT
	300 num UNION ALL
SELECT
	400 num UNION ALL
SELECT
	500 num UNION ALL
SELECT
	600 num UNION ALL
SELECT
	700 num UNION ALL
SELECT
	800 num UNION ALL
SELECT
	900 num 
	) c,
	(
SELECT
	0 num UNION ALL
SELECT
	1000 num UNION ALL
SELECT
	2000 num UNION ALL
SELECT
	3000 num UNION ALL
SELECT
	4000 num UNION ALL
SELECT
	5000 num UNION ALL
SELECT
	6000 num UNION ALL
SELECT
	7000 num UNION ALL
SELECT
	8000 num UNION ALL
SELECT
	9000 num 
	) d,
	(
SELECT
	0 num UNION ALL
SELECT
	10000 num UNION ALL
SELECT
	20000 num UNION ALL
SELECT
	30000 num UNION ALL
SELECT
	40000 num UNION ALL
SELECT
	50000 num UNION ALL
SELECT
	60000 num UNION ALL
SELECT
	70000 num UNION ALL
SELECT
	80000 num UNION ALL
SELECT
	90000 num 
	) e 
	) t0 
WHERE
	DATE( '2023-11-01' ) + INTERVAL t0.num DAY <= '2023-11-31' 
	) date_range
	LEFT JOIN user_order oco ON date_range.date = DATE( oco.create_time ) 
	AND oco.company_id = 15 
GROUP BY
	date_range.date 
ORDER BY
	date_range.date ASC;