时间戳

首页 > 解决方案 > 如何在sql中的数据库中获取记录的日期和未记录的日期

mysql - 如何在sql中的数据库中获取记录的日期和未记录的日期

问题描述

我写了一个查询来获取数据库中所有天之间的两天，但我的要求是获取记录的日期而不是数据库中记录的天数。现在这个查询输出只是数据库中记录的日期。我需要在两个日期之间得到所有的日子

我的查询是

SELECT COUNT(activity_detail.activity_type_config_id) As count,
user_det.full_name, DATEPART(day, activity_detail.created_date) AS day,
DATEPART(month, activity_detail.created_date) AS month,
DATEPART(year, activity_detail.created_date) AS year
FROM activity_detail activity_detail,activity_type_config activity_type_config,activity_user user_det 
WHERE activity_detail.activity_type_config_id = activity_type_config.activity_type_config_id and user_det.activity_user_id = activity_detail.activity_user_id
AND activity_detail.created_date BETWEEN '2021-01-08 10:18:13' AND '2021-11-17 10:40:09' 
GROUP BY user_det.full_name,DATEPART(month, activity_detail.created_date),DATEPART(day, activity_detail.created_date),DATEPART(year, activity_detail.created_date)
ORDER BY DATEPART(year, activity_detail.created_date) ASC, DATEPART(month, activity_detail.created_date) ASC,DATEPART(day, activity_detail.created_date)

输出

count name day month year
1   john1   8   8    2021
1   carter  11  8   2021
1   john1   15  9   2021
4   john1   18  9   2021
7   carter  18  9   2021
1   john1   19  9   2021
2   carter  19  9   2021
3   john1   20  9   2021
42  carter21    9   2021
1   john1   21  9   2021

我需要输出

count name day month year
0   null    1   8    2021
0   null    2   8   2021...
1   john1   8   8    2021
1   carter  11  8   2021
0   null    1   9   2021
0   null    2   9   2021
0   null    3   9   2021...
1   john1   15  9   2021
4   john1   18  9   2021
7   carter  18  9   2021
1   john1   19  9   2021
2   carter  19  9   2021
3   john1   20  9   2021
0   null  16    11  2021
0   null    17  11  2021

未记录天数结果必须为 0，null 但这些不在数据库中。帮助获取所有日期。

标签： mysql

解决方案

它将类似于（未调试）

WITH RECURSIVE
nums AS ( SELECT 1 num
          UNION ALL
          SELECT num + 1 FROM nums WHERE num < 31 ),
ranges AS ( SELECT MIN(day) day, month, year
            FROM table
            GROUP BY 2,3 )
data AS ( {your query text} ) 
SELECT count, name, day, month, year
FROM data
UNION ALL
SELECT 0, NULL, nums.num, ranges.month, ranges.year
FROM nums
JOIN ranges ON nums.num < ranges.day

推荐阅读