mysql - 在 MySQL 中不显示 0 计数的简单修复

Question

我做了这个查询，计算每小时有多少新插入。问题是我的查询不会显示没有新条目的时间。

如何轻松解决此问题？

SELECT
count(HOUR(delivered))as count,
CASE 
    WHEN HOUR(delivered) = 0 THEN '0'
    WHEN HOUR(delivered) = 1 THEN '1'
    WHEN HOUR(delivered) = 2 THEN '2'
    WHEN HOUR(delivered) = 3 THEN '3'
    WHEN HOUR(delivered) = 4 THEN '4'
    WHEN HOUR(delivered) = 5 THEN '5'
    WHEN HOUR(delivered) = 6 THEN '6'
    WHEN HOUR(delivered) = 7 THEN '7'
    WHEN HOUR(delivered) = 8 THEN '8'
    WHEN HOUR(delivered) = 9 THEN '9'
    WHEN HOUR(delivered) = 10 THEN '10'
    WHEN HOUR(delivered) = 11 THEN '11'
    WHEN HOUR(delivered) = 12 THEN '12'
    WHEN HOUR(delivered) = 13 THEN '13'
    WHEN HOUR(delivered) = 14 THEN '14'
    WHEN HOUR(delivered) = 15 THEN '15'
    WHEN HOUR(delivered) = 16 THEN '16'
    WHEN HOUR(delivered) = 17 THEN '17'
    WHEN HOUR(delivered) = 18 THEN '18'
    WHEN HOUR(delivered) = 19 THEN '19'
    WHEN HOUR(delivered) = 20 THEN '20'
    WHEN HOUR(delivered) = 21 THEN '21'
    WHEN HOUR(delivered) = 22 THEN '22'
    WHEN HOUR(delivered) = 23 THEN '23'

END AS intervals
FROM
    Wardrobe_CloakTable
WHERE
    payingcustomerID = 2
    AND DAY(delivered) = 09
    AND MONTH(delivered) = 09
    AND YEAR(delivered) = 2016
GROUP BY intervals

Answer 1

您可以从一个包含 0 到 23 之间的值的表开始，然后左连接您的数据表。

编辑:让我们尝试将 COUNT(delivered) 更改为 SUM(delivered IS NOT NULL) 看看我们是否可以获得数值在每一行。

让我们从简化您的查询开始。这很快就会派上用场，因为我们必须使用它来进行更大的查询。

            SELECT SUM(delivered IS NOT NULL) AS count,
                   HOUR(delivered) AS intervals
              FROM Wardrobe_CloakTable
             WHERE payingcustomerID = 2
               AND delivered >= '2016-09-09'
               AND delivered <  '2016-09-09' + INTERVAL 1 DAY
             GROUP BY HOUR(delivered)

(顺便说一下，(payingcustomerID, delivered) 上的索引将使这个查询非常快。)

现在我们需要一个带有数字 0 - 23 的小虚拟表。这将起到作用:它使用 JOIN 组合生成 2 x 2 x 2 x 3 整数。我们将这个小表称为 seq_0_to_23。为什么？因为这些sequence tables内置于 MariaDB MySQL 的分支。

          SELECT A.N + 2*(B.N + 2*(C.N +2*(D.N))) AS seq
            FROM (SELECT 0 AS N UNION SELECT 1) AS A
            JOIN (SELECT 0 AS N UNION SELECT 1) AS B
            JOIN (SELECT 0 AS N UNION SELECT 1) AS C
            JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) AS D

最后，您使用 LEFT JOIN，就像这样。

SELECT SUM(delivered IS NOT NULL) AS count,
       seq AS intervals
 FROM (
                SELECT A.N + 2*(B.N + 2*(C.N +2*(D.N))) AS seq
                  FROM (SELECT 0 AS N UNION SELECT 1) AS A
                  JOIN (SELECT 0 AS N UNION SELECT 1) AS B
                  JOIN (SELECT 0 AS N UNION SELECT 1) AS C
                  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2) AS D
       ) seq_0_to_23
  LEFT JOIN Wardrobe_CloakTable ON HOUR(delivered) = seq
 WHERE payingcustomerID = 2
   AND delivered >= '2016-09-09'
   AND delivered <  '2016-09-09' + INTERVAL 1 DAY
 GROUP BY seq
 ORDER BY seq

这使用序列表作为一天中所有时间的来源，然后从您的实际数据中提取匹配值。

如果您使用的是 MariaDB，则看起来是这样。如您所见，内置的序列表使查询看起来更加优雅。

SELECT SUM(delivered IS NOT NULL) AS count,
       seq AS intervals
  FROM seq_0_to_23
  LEFT JOIN Wardrobe_CloakTable ON HOUR(delivered) = seq
 WHERE payingcustomerID = 2
   AND delivered >= '2016-09-09'
   AND delivered <  '2016-09-09' + INTERVAL 1 DAY
 GROUP BY seq
 ORDER BY seq

我们使用 SELECT SUM(delivered IS NOT NULL) 因为我们需要聚合函数中每一行的数字结果。 delivered IS NOT NULL 返回 1 或 0，因此 SUM() 应该可以很好地工作。

这是一个 comprehensive explanation of this technique.