MySQL:按小时分组，需要显示所有小时，没有数据时为null

Question

这是查询:

SELECT h.idhour, h.`hour`, outnumber, count(*) as `count`, sum(talktime) as `duration` 
FROM (
     SELECT 
        `cdrs`.`dcustomer` AS `dcustomer`,
        (CASE
            WHEN (`cdrs`.`cnumber` like "02%") THEN '02'
            WHEN (`cdrs`.`cnumber` like "05%") THEN '05'
        END) AS `outnumber`,
        FROM_UNIXTIME(`cdrs`.`start`) AS `start`,
         (`cdrs`.`end` - `cdrs`.`start`) AS `duration`,
         `cdrs`.`talktime` AS `talktime`
    FROM `cdrs`
    WHERE `cdrs`.`start` >= @_START and `cdrs`.`start` < @_END
    AND `cdrs`.`dtype` = _LATIN1'external'
    GROUP BY callid
   ) cdr 

   JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN 
        (SELECT id FROM customers WHERE parent = _ID))

   GROUP BY h.idhour, cdr.outnumber
   ORDER BY h.idhour;

上面的查询结果跳过了没有数据的时间，但我需要显示具有 null 或 0 值的所有时间(00:00 到 23:00)。我怎样才能做到这一点？

Answer 1

SELECT h.idhour
, h.hour
,IFNULL(outnumber,'') AS outnumber
,IFNULL(cdr2.duration,0) AS duration
,IFNULL(output_count,0) AS output_count
FROM hub.hours h
  LEFT JOIN ( 
 SELECT HOUR(start) AS start,outnumber, SUM(talktime) as duration ,COUNT(1) AS output_count
 FROM
 (
 SELECT cdrs.dcustomer AS dcustomer
 , (CASE WHEN (cdrs.cnumber like "02%") THEN '02' WHEN (cdrs.cnumber like "05%") THEN '05' END) AS outnumber
 , FROM_UNIXTIME(cdrs.start) AS start
 , (cdrs.end - cdrs.start) AS duration
 , cdrs.talktime AS talktime 
 FROM cdrs cdrs
 INNER JOIN customers c ON c.id = cdrs.dcustomer 
 WHERE cdrs.start >= @_START and cdrs.start < @_END AND cdrs.dtype = _LATIN1'external' 
 AND
 (c.parent = _ID or cdrs.dcustomer = _ID or c.parent IN (SELECT id FROM customers WHERE parent = _ID))
 GROUP BY callid 
 ) cdr 
 GROUP BY HOUR(start),outnumber
 ) cdr2 
 ON cdr2.start = h.idhour
 ORDER BY h.idhour