为什么mysql查询没有记录,MySQL查询-包括没有记录的日期

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)

解决方案

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`)

);

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 and counting not null values from right table's ensures that the count is correct (0 if no row exists for a given date).