每天的所有时间

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 

我的问题是查询只返回dbo.Orders.Date中的现有小时数。
例如：

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

您可以使用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 

Answer 3

A“号码表”（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 

（我这个措辞根据您的例子为清楚起见，但在实践中我会尽量避免把一个函数在连接标准，以最大限度地提高性能。）