Question

在我的sql查询中，我计算一天中每个小时的订单数量。我的查询如下所示：

SELECT   COUNT(dbo.Uputa.ID),{ fn HOUR(dbo.Orders.Date) } AS Hour
FROM      Orders       
WHERE dbo.Orders.Date BETWEEN  2011-05-01  AND  2011-05-26  
GROUP BY { fn HOUR(dbo.Orders.Date) }
ORDER BY Hour

My problem is that the query returns only existing Hours in dbo.Orders.Date.
For example:

Number   Hour
12         3
12         5

我想像这样返回所有时间：

 Number      Hour
    0          0
    0          1
    0          2
    12         3
    0          4
    12         5
   ...
    0          23

有人知道如何做到这一点吗？

Answer 1

使用通用表表达式创建所有小时数，然后左联接分组总数以获得结果。

with mycte as
(
    SELECT 0 AS MyHour
    UNION ALL
    SELECT MyHour + 1
    FROM mycte 
    WHERE MyHour + 1 < 24
)
SELECT mycte.MyHour, COALESCE(OrderCount,0) FROM mycte
LEFT JOIN 
(
    SELECT  COUNT(dbo.Uputa.ID) AS OrderCount,{ fn HOUR(dbo.Orders.Date) } AS MyHour
    FROM    Orders       
    WHERE   dbo.Orders.Date BETWEEN  2011-05-01  AND  2011-05-26  
    GROUP BY { fn HOUR(dbo.Orders.Date) }
) h
ON
  h.MyHour = mycte.MyHour;

Answer 2

数字表（SQL，例如Auxiliary table of numbers）通常是数据库中非常有用的东西；如果您在这里创建一个，您可以从数字表中选择0到23之间的所有行，将其与结果左连接，您将获得所需的结果，而无需纯粹为此查询创建自定义CTE或类似内容。

SELECT   COUNT(dbo.Uputa.ID),n.number AS Hour
FROM     (select number from numbers where number between 0 and 23) n 
             left join Orders o on n.number={ fn HOUR(dbo.Orders.Date) }
WHERE dbo.Orders.Date BETWEEN  2011-05-01  AND  2011-05-26  
GROUP BY n.number
ORDER BY n.number

（为了清楚起见，我已经按照你的例子来表达了这一点，但在实践中，我会尽量避免在加入标准中加入一个函数，以最大限度地提高性能。）

Answer 3

您可以使用CTE添加丢失的小时数，JOIN将这些小时数与您的原始查询连接以填补空白。

SQL语句

;WITH q (Number, Hour) AS (
    SELECT  0, 1
    UNION ALL
    SELECT  q.Number, q.Hour + 1
    FROM    q
    WHERE   q.Hour < 23
)
SELECT  COALESCE(o.Number, q.Number)
        , q.Hour
FROM    q
        LEFT OUTER JOIN (
            SELECT   COUNT(dbo.Uputa.ID),{ fn HOUR(dbo.Orders.Date) } AS Hour
            FROM      Orders       
            WHERE dbo.Orders.Date BETWEEN  2011-05-01  AND  2011-05-26  
            GROUP BY { fn HOUR(dbo.Orders.Date) }
        ) o ON o.Hour = q.Hour
ORDER BY 
        q.Hour

测试脚本

;WITH Orders (Number, Hour) AS (
    SELECT 12, 3
    UNION ALL SELECT 12, 5
)
, q (Number, Hour) AS (
    SELECT  0, 1
    UNION ALL
    SELECT  q.Number, q.Hour + 1
    FROM    q
    WHERE   q.Hour < 23
)
SELECT  COALESCE(o.Number, q.Number)
        , q.Hour
FROM    q
        LEFT OUTER JOIN Orders o ON o.Hour = q.Hour

友情链接