如何填补 MySQL 中的日期空白?这是我的查询:
<前>SELECT DATE(posted_at) AS 日期,COUNT(*) AS 总数,SUM(attitude = 'positive') 为正,SUM(attitude = 'neutral') 作为中立,SUM(attitude = 'negative') AS 负数从`消息`WHERE (`messages`.brand_id = 1)AND (`messages`.`spam` = 0AND `messages`.`duplicate` = 0AND `messages`.`ignore` = 0)GROUP BY 日期 ORDER BY 日期它返回正确的结果集 - 但我想用零填充日期开始和结束之间的空白.我该怎么做?
您需要创建一个辅助表并用从 start
到 end
的所有日期填充它,然后只需 LEFT JOIN
与该表:
SELECT d.dt AS 日期,COUNT(*) AS 总数,SUM(attitude = 'positive') 为正,SUM(attitude = 'neutral') 作为中立,SUM(attitude = 'negative') AS 负数FROM 日期 d左加入留言ON m.posted_at >= d.dtAND m.posted_at
基本上,您在这里需要的是一个虚拟行源.
MySQL
是唯一缺乏生成它的方法的主要系统.
PostgreSQL
实现了一个特殊的函数 generate_series
来做到这一点,而 Oracle
和 SQL Server
可以使用递归(CONNECT BY
和递归CTE
,相应地).
How i can fill date gaps in MySQL? Here is my query:
SELECT DATE(posted_at) AS date, COUNT(*) AS total, SUM(attitude = 'positive') AS positive, SUM(attitude = 'neutral') AS neutral, SUM(attitude = 'negative') AS negative FROM `messages` WHERE (`messages`.brand_id = 1) AND (`messages`.`spam` = 0 AND `messages`.`duplicate` = 0 AND `messages`.`ignore` = 0) GROUP BY date ORDER BY date
It returns proper result set - but i want to fill gaps between dates start and end by zeros. How i can do this?
You'll need to create a helper table and fill it with all dates from start
to end
, then just LEFT JOIN
with that table:
SELECT d.dt AS date,
COUNT(*) AS total,
SUM(attitude = 'positive') AS positive,
SUM(attitude = 'neutral') AS neutral,
SUM(attitude = 'negative') AS negative
FROM dates d
LEFT JOIN
messages m
ON m.posted_at >= d.dt
AND m.posted_at < d.dt + INTERVAL 1 DAYS
AND spam = 0
AND duplicate = 0
AND ignore = 0
GROUP BY
d.dt
ORDER BY
d.dt
Basically, what you need here is a dummy rowsource.
MySQL
is the only major system which lacks a way to generate it.
PostgreSQL
implements a special function generate_series
to do that, while Oracle
and SQL Server
can use recursion (CONNECT BY
and recursive CTE
s, accordingly).
这篇关于如何填补 MySQL 中的日期空白?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!