Mysql最近30天的日期

Question

我需要帮助在mysql 中处理一些日期

我有一个查询，它会给我带来过去30天内每条消息对应日期的消息计数。

SELECT DATE_FORMAT(time, '%m/%d/%Y') AS Dates, count(*) as count 
FROM ma_messages 
WHERE time BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
AND usersid_send = 110 
Group by Dates 
ORDER BY Dates ASC

查询图像

但我还需要它给我带来最近30天中计数为0的其他日期，例如，在查询中还给我带来2021年5月26日的日期以及2021年5日25日之前和2021年5年28日之后的日期。

我不知道这是否可能，但我会寻求任何帮助。

谢谢。

Answer 1

尝试这个

SELECT 
DATE_FORMAT(time, '%m/%d/%Y') AS Dates, 
count(if(usersid_send='110',true,null)) as count 
FROM ma_messages 
WHERE time BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
Group by Dates 
ORDER BY Dates ASC

Answer 2

我有一个日期表。。一年中的所有日期——Alexis Murillo

SELECT d.`date`, COUNT(m.`time`) AS `count`
FROM dates_table d
LEFT JOIN ma_messages m ON d.`date` = DATE(m.`time`)
WHERE d.`date` BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
AND m.usersid_send = 110 
GROUP BY d.`date`
ORDER BY d.`date` ASC

Answer 3

使用您的表"一年中的所有日期">：

WITH
message_counts AS (  SELECT DATE_FORMAT("time", '%m/%d/%Y') AS "day", 
COUNT(*) AS "count" 
FROM ma_messages 
WHERE "time" BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
AND
usersid_send = 110 
GROUP BY Dates)                         
SELECT all_dates."day", COALESCE(message_counts."count", 0)
FROM all_dates
LEFT JOIN message_counts
ON all_dates."day" = message_counts."day"
WHERE all_dates."day" BETWEEN BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
ORDER BY all_dates."day" ASC