返回最近N天的记录，何时N中的每一天都没有记录? [英] Return records for last N days, when not every day in N has a record?

问题描述

我想检索最近7天的用户活动.我要查询的表是多对多的，链接用户，事件和日期.

I'd like to retrieve the activity of a user from the last 7 days. The table I'm querying is a many-to-many, linking users, events and dates.

如果今天是5月7日，但是用户只记录了5月4日和2月2日的活动，我是否可以构建一个查询，该查询将返回与未记录活动日期相对应的NULL?

If today is the 7th of May, but the user only has activity recorded for the 4th and 2nd of May, can I build a query that will return a NULL corresponding with the dates of no activity recorded?

来源

May 2     42
May 4     88

所需的输出

May 1     NULL
May 2     42
May 3     NULL
May 4     88
May 5     NULL
May 6     NULL
May 7     NULL

如何在MySQL中完成此操作?

How can this be done in MySQL?

推荐答案

SELECT DISTINCT users, events, dates
FROM table
WHERE dates between date_format(date1, '%Y-%m-%d')
and date_format(date2, '%Y-%m-%d')    or NOW() or CURDATE()....
and user = 'username'

这很笼统，但是如果替换名称和日期(可能不需要格式化)，无论空位占位符如何，这将返回所有结果

This is pretty general, but if you replace the names and the dates (probably don't need to format for your purposes), this will return all results regardless of null place holders

您可以填充一个date_reference_table ...这是为此存储的proc:

You could populate a date_reference_table...here is the stored proc for this:

我使用日期格式仅识别年份和月份，但是您可以更改此格式...

I use a date format to only recognize the year and month, but you can alter this...

然后，您还需要一个内部联接...，因此您将在date_reference表上进行内部联接，它会吐出上面评论中提到的结果.

You would also need an inner join then... so you would do an inner join on the date_reference table and it will spit out the results as mentioned in the comment above.

定界符$$

CREATE PROCEDURE `generate_date_reference_table`(d1 date, d2 date)
BEGIN
declare d datetime;

create table BLMBP.date_reference (d char(7) not null);

set d = d1;
while d <= d2 do
    insert into BLMBP.date_reference (d) values (date_format(d, '%Y-%m'));
    set d = date_add(d, interval 1 month);
end while;

END$$

这篇关于返回最近N天的记录，何时N中的每一天都没有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！