MySQL查询-包括没有记录的日期 [英] MySQL Query - Include dates without records

问题描述

我有一个显示图形的报告. X轴使用以下查询中的日期.如果查询不返回任何日期，我会出现空白，并且希望返回一个值.有什么办法可以强制没有记录的日期?

I have a report that displays a graph. The X axis uses the date from the below query. Where the query returns no date, I am getting gaps and would prefer to return a value. Is there any way to force a date where there are no records?

SELECT
    DATE(instime),
    CASE
        WHEN direction = 1 AND duration > 0 THEN 'Incoming'
        WHEN direction = 2 THEN 'Outgoing'
        WHEN direction = 1 AND duration = 0 THEN 'Missed'
    END AS type,
    COUNT(*)
FROM taxticketitem
GROUP BY
    DATE(instime),
    CASE
        WHEN direction = 1 AND duration > 0 THEN 'Incoming'
        WHEN direction = 2 THEN 'Outgoing'
        WHEN direction = 1 AND duration = 0 THEN 'Missed'
    END
ORDER BY DATE(instime)

推荐答案

一种可能的方法是创建一个日期表，并用它们创建LEFT JOIN表.该表可能如下所示:

One possible way is to create a table of dates and LEFT JOIN your table with them. The table could look something like this:

CREATE TABLE `datelist` (
 `date` DATE NOT NULL,
 PRIMARY KEY (`date`)
);

并填满所有日期，例如2000年1月1日到12月31日至2050年之间的日期(这是我的 日期生成器脚本 ).

and filled with all dates between, say Jan-01-2000 through Dec-31-2050 (here is my Date Generator script).

接下来，像这样编写您的查询:

Next, write your query like this:

SELECT datelist.date, COUNT(taxticketitem.id) AS c
FROM datelist
LEFT JOIN taxticketitem ON datelist.date = DATE(taxticketitem.instime)
WHERE datelist.date BETWEEN `2012-01-01` AND `2012-12-31`
GROUP BY datelist.date
ORDER BY datelist.date

LEFT JOIN并从右表中不算空值可确保计数正确(如果在给定日期不存在任何行，则为0).

LEFT JOIN and counting not null values from right table's ensures that the count is correct (0 if no row exists for a given date).

这篇关于MySQL查询-包括没有记录的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！