近期有个业务,根据当前日期查询近一周预约数量
举例:
张三 开始日期:2024-01-29 结束日期:2024-01-30
李四 开始日期:2024-01-30 结束日期:2024-02-01
王五 开始日期:2024-02-02 结束日期:2024-02-02
需求:开始日期和结束日期包括当前日期都统计出来
日期 | 数量 |
---|---|
2024-01-27 | 0 |
2024-01-28 | 0 |
2024-01-29 | 1 |
2024-01-30 | 2 |
2024-01-31 | 1 |
2024-02-01 | 1 |
2024-02-02 | 1 |
以这种形式展现
WITH RECURSIVE datetime AS (
SELECT
DATE_SUB( CURDATE(), INTERVAL n DAY ) AS date
FROM
(
SELECT
0 AS n UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5 UNION ALL
SELECT
6
) AS days
ORDER BY
n
) SELECT
d.date AS yytime,
IFNULL( t.num, 0 ) AS num
FROM
datetime d
LEFT JOIN (
SELECT
da.date,
count(*) AS num
FROM
datetime da
JOIN person per ON da.date BETWEEN per.start_time
AND per.end_time
GROUP BY
da.date
) t ON d.date = t.date
ORDER BY
d.date
下面这段SQL使用的硬编码,伙伴们可根据需要修改
SELECT
0 AS n UNION ALL
SELECT
1 UNION ALL
SELECT
2 UNION ALL
SELECT
3 UNION ALL
SELECT
4 UNION ALL
SELECT
5 UNION ALL
SELECT
6
结果:
还有更好方法的小伙伴们可以在评论区留言。