在 SQL 中计算每年的事件数

Question

我有一个有日期的事件列表。我试图在单个语句中使用简单的联接、选择等（无子查询）来计算 mySQL 中今年发生的事件数量，以及两侧 5 年发生的事件数量（无论是否发生任何事件）。

我有一张表，可以生成年份和当年的事件数量，但当该年没有发生事件时就会遇到问题

Answer 1

查看 mysql 上的日期函数 http:// /dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

您可以使用 datediff 这会给您带来天数差异。前任;

其中abs(datediff(now(), event_date)) < 365*5

或 dateadd()，如果您的事件日期是时间戳，请使用 timestampdiff()

示例查询

SELECT count(*) FROM mytable
WHERE abs(datediff(now(), event_date)) < 365*5

UPDATE

基于我在这里读到的一些评论，这里有一个查询供您

SELECT year(event_date) as event_year, count(event_date)
FROM mytable
WHERE
abs(datediff(now(), event_date)) < 365*5
GROUP by year(event_date)

随意调整5 in (365 * 5) for different range

UPDATE 2

这不是很漂亮，但你可以用纯mysql尝试这个。如果需要，您还可以将其修改为存储过程：

SET @y6 = year(now());
SET @y5 = @y6-1;
SET @y4 = @y5-1;
SET @y3 = @y4-1;
SET @y2 = @y3-1;
SET @y1 = @y2-1;

SET @y7 = @y6+1;
SET @y8 = @y7+1;
SET @y9 = @y8+1;
SET @y10 = @y9+1;
SET @y11 = @y10+1;

CREATE TEMPORARY TABLE event_years (event_year int not null);
INSERT INTO event_years SELECT @y1;
INSERT INTO event_years SELECT @y2;
INSERT INTO event_years SELECT @y3;
INSERT INTO event_years SELECT @y4;
INSERT INTO event_years SELECT @y5;
INSERT INTO event_years SELECT @y6;
INSERT INTO event_years SELECT @y7;
INSERT INTO event_years SELECT @y8;
INSERT INTO event_years SELECT @y9;
INSERT INTO event_years SELECT @y10;
INSERT INTO event_years SELECT @y11;

SELECT ey.event_year , (SELECT count(event_date) from mytable where year(event_date) = ey.event_year)
from event_years ey;

连接关闭后临时表将自行删除。如果在 SELECT 之后添加 DROP TABLE，则可能无法取回结果。

Answer 2

您尝试过使用左连接吗？

修改：

SELECT tleft.YEARS, COUNT(tright.EVENTS) 
FROM ONLY_YEARS tleft LEFT JOIN TABLE1 tright 
ON (tleft.YEARS = tright.YEARS)
GROUP BY tleft.YEARS;

通过此修改，您需要指向一个包含所有年份 (ONLY_YEARS) 的表，可能是一个包含从 1990 年到 2020 年的一列的虚拟表...

MySQL左连接优化链接

Answer 3

要选择两年之间发生的事件的计数（按年份分组），以下 sql 应该足够了：

select year(event.date), count(*) from event where event.date >= '2006' and event.date <= '2016' group by year(event.date);

但是，如果一年内没有发生任何事件，则不会返回任何结果。
数据库并不是真正为这种动态事物而设计的，我建议这种逻辑应该放在业务（或可能是数据访问）层中。