统计数据自动补全不存在日期 天

// 获取列表数据 sql
SELECT
FROM_UNIXTIME(add_time, ‘%Y-%m-%d’) add_time,
attr_name,
product_id,
COUNT(*) total
FROM
cd_order
WHERE pay_status = 2
AND add_time > 1664616618
AND add_time < 1667295018
GROUP BY FROM_UNIXTIME(add_time, ‘%Y-%m-%d’)

// 获取日期sql
SELECT
(
@date := DATE_ADD(@date, INTERVAL + 1 DAY)
) AS dates
FROM
cd_order,
(SELECT
@date := ‘2022-09-30’) temp
WHERE @date < ‘2022-10-31’ ;

// 组合sql 获得最终数据
SELECT
dates,
IFNULL(t1.product_id, 17) product_id,
IFNULL(t1.attr_name, ‘三分复力’) attr_name,
IFNULL(t1.total, 0) total_num,
t1.add_time
FROM
(SELECT
FROM_UNIXTIME(add_time, ‘%Y-%m-%d’) add_time,
attr_name,
product_id,
COUNT(*) total
FROM
cd_order
WHERE pay_status = 2
AND add_time > 1664616618
AND add_time < 1667295018
GROUP BY FROM_UNIXTIME(add_time, ‘%Y-%m-%d’)) t1
RIGHT JOIN
(SELECT
@date := DATE_ADD(@date, INTERVAL + 1 DAY) dates
FROM cd_order,
(SELECT
@date :=‘2022-09-30’) t2
WHERE @date < ‘2022-10-31’) t3
ON t3.dates = t1.add_time
GROUP BY dates
ORDER BY dates ASC;