sql - 在没有查询的返回值中插入日期

Question

我们正在构建一个查询来计算每天每小时的事件数。大多数日子里有几个小时没有任何事件，因此在运行查询的地方会显示每小时的事件计数，但存在间隙并且查询排除了这些。我们仍然希望显示没有事件的小时数并显示零，以便可以绘制零值。我们使用的查询看起来像这样……

select datepart(Year, dev_time) as Year,
    datepart(Month, dev_time) as Month,
    datepart(Day, dev_time) as Day,
    datepart(Hour, dev_time) as Hour,
    count(tdm_msg) as Total_ACTIVITES
from TCKT_ACT
where tdm_msg = ‘4162′ and dev_time >= DATEADD(day, - 1, GETDATE())
group by datepart(Year, dev_time) ,
    datepart(Month, dev_time) ,
    datepart(Day, dev_time),
    datepart(Hour, dev_time)
order by datepart(Year, dev_time) asc,
    datepart(Month, dev_time) asc,
    datepart(Day, dev_time) asc,
    datepart(Hour, dev_time) asc

Answer 1

您将以某种方式需要一个包含天数和小时数的表，然后您必须在该表和您的查询之间进行外部连接。这是我将如何做到的。请注意，此解决方案仅适用于 SQL Server 2005 和 2008。如果您没有这些平台，则必须在您的数据库中实际创建一个时间表，您可以从中加入:

DECLARE @MinDate DATETIME;
SET @MinDate =  CONVERT(varchar, GETDATE(), 101);

WITH times AS (
    SELECT @MinDate as dt, 1 as depth
    UNION ALL
    SELECT DATEADD(hh, depth, @MinDate), 1 + depth as depth
    FROM times
    WHERE DATEADD(hh, depth, @MinDate) <= GETDATE())
SELECT DATEPART(YEAR, t.dt) as [Year],
    DATEPART(MONTH, t.dt) as [Month],
    DATEPART(DAY, t.dt) as [Day],
    DATEPART(HOUR, t.dt) as [Hour],
    COUNT(tdm_msg) as Total_ACTIVITES
FROM times t
LEFT JOIN (SELECT * FROM TCKT_ACT WHERE tdm_msg = '4162' and dev_time >= @MinDate) a
    ON  DATEPART(HOUR, t.dt)  = DATEPART(HOUR, a.dev_time)
    AND MONTH(t.dt) = MONTH(a.dev_time)
    AND DAY(t.dt)   = DAY(a.dev_time)
    AND YEAR(t.dt)  = YEAR(a.dev_time)
GROUP BY DATEPART(YEAR, t.dt) ,
    DATEPART(MONTH, t.dt) ,
    DATEPART(DAY, t.dt),
    DATEPART(HOUR, t.dt)
ORDER BY DATEPART(YEAR, t.dt) asc,
    DATEPART(MONTH, t.dt) asc,
    DATEPART(DAY, t.dt) asc,
    DATEPART(HOUR, t.dt) asc
OPTION (MAXRECURSION 0); /* Just in case you want a longer timespan later on... */

请注意，顶部的 WITH 语句称为递归公用表表达式，是生成元素数量相对较少的顺序表的好方法，就像您在此处看到的那样。