如何填补MySQL中的日期空白?

本文介绍了如何填补MySQL中的日期空白?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我如何填补MySQL中的日期空白?这是我的查询:

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?

推荐答案

您需要创建一个帮助器表，并用从start到end的所有日期填充该表，然后在该表中仅LEFT JOIN:

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是唯一缺少生成它的方法的主要系统.

MySQL is the only major system which lacks a way to generate it.

PostgreSQL实现了特殊的功能generate_series来做到这一点，而Oracle和SQL Server可以使用递归(相应地，CONNECT BY和递归CTE s).

PostgreSQL implements a special function generate_series to do that, while Oracle and SQL Server can use recursion (CONNECT BY and recursive CTEs, accordingly).