代码之家 › 专栏 › 技术社区 › JR Lawhorne

SQL查询与日期范围中的日期匹配的记录数?

mysql sql

JR Lawhorne · 技术社区 · 15 年前

CREATE TABLE sample (
  ix int unsigned auto_increment primary key,
  start_active datetime,
  last_active datetime
);

我使用的是MySQL,查询将从PHP运行,但我并不真正需要PHP代码,只需要查询。

这是我的开始:

SELECT COUNT(1) cnt, DATE(?each of last 30 days?) adate
FROM sample
WHERE adate BETWEEN start_active AND last_active
GROUP BY adate;

3 回复 | 直到 15 年前

tpdi 15 年前

没有桌子吗?做一张桌子。为了这个,我总是在附近放一张假桌子。

create table artificial_range( 
  id int not null primary key auto_increment, 
  name varchar( 20 ) null ) ;

-- or whatever your database requires for an auto increment column

insert into artificial_range( name ) values ( null )
-- create one row.

insert into artificial_range( name ) select name from artificial_range;
-- you now have two rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have four rows

insert into artificial_range( name ) select name from artificial_range;
-- you now have eight rows

--etc.

insert into artificial_range( name ) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024

现在,使其便于使用,并将其限制在30天内,以期:

编辑:JR劳霍恩注释:

您需要将“date\u add”更改为“date\u sub”,以获取已创建视图中的前30天。

谢谢JR!

create view each_of_the_last_30_days as
select date_sub( now(), interval (id - 1) day ) as adate
from artificial_range where id < 32;

现在在查询中使用它(我还没有实际测试过) 查询,我只是假设它工作正常):

编辑:我应该以另一种方式加入:

SELECT COUNT(*) cnt, b.adate
FROM  each_of_the_last_30_days b
left outer join sample a 
 on ( b.adate BETWEEN a.start_active AND a.last_active)
GROUP BY b.adate;

Bill Karwin 15 年前

SQL在匹配存储在数据库中的值集方面非常出色,但在匹配不是在数据库中。因此,一个简单的解决方法是创建一个包含所需值的临时表:

CREATE TEMPORARY TABLE days_ago (d SMALLINT);
INSERT INTO days_ago (d) VALUES
  (0), (1), (2), ... (29), (30);

现在,您可以比较 d 几天前到 start_active last_active 每行的。计算每个值的组中有多少匹配行 D 还有你的计数。

SELECT CURRENT_DATE - d DAYS, COUNT(*) cnt, 
FROM days_ago
 LEFT JOIN sample ON (CURRENT_DATE - d DAYS BETWEEN start_active AND last_active)
GROUP BY d
ORDER BY d DESC; -- oldest to newest

GROUP BY 条款实际上,在标准SQL中,只有在 ORDER BY 子句,但MySQL支持在中使用别名 分组 HAVING 条款也是如此。

James Black 15 年前

在查询中,将日期转换为unix时间戳,即秒,然后只需查找差异为<=一个月内的秒数。

[编辑]由于我对真正的问题感到困惑,我需要完成草坪,但在我忘记之前,我想把这个写下来。

要按天计算数字,您需要将where子句设置为如上所述,限制为过去30天,但您需要按天分组,因此通过将每个开始转换为月份的某一天来进行选择,然后进行计数。

这假设每次使用将限制在一天内,如果开始和结束日期可以跨越几天,那么它将更加棘手。