sql表按小时选择

Question

我有一个包含4列的安全日志表：

UserID, LOGINDate, LOGINTime, ClickEvents

现在我正试图获得过去7天的每小时流量表，它是这样的：

   DAY1 |  DAY2 | DAY3 |  DAY4 |.... |DAY7
1   0   |    1  |   12 |  4567 |     | 43
2
3
4
5
:
:
24

你能告诉我或者给我一些如何使用SQL制作这个表的想法吗？

---

谢谢你的快速回复。我现在拥有的是：

select  LOGINDate, SUBSTRING(LOGINTime, 1, 2) as 'HoTime', COUNT( *)
From SECLOG
where (CONVERT(varchar( 8) , GETDATE()-7, 112) <= LOGINDate)
group by LOGINDate, SUBSTRING(LOGINTime, 1, 2)
order by LOGINDate, HoTime

它为我制作了一张类似的表格

DATE | HoTime | No of
0926 | 1      | 2
0926 | 2      | 4
0926 | 14     | 6

而且它在没有数据的情况下跳过一个小时。

Answer 1

首先执行一个查询，按日期和小时对过去7天中每小时的点击事件进行分组
然后你需要调整结果，这样你就可以把天数作为一列。这可以通过一个小组在小时内完成，并在字段列表中使用案例语句测试日期。

当一小时内没有事件时，要将零作为一个值，可以使用返回1-24并左加入结果集的数字表。

类似于SQL Server 2008中的日期数据类型。

with C as
(
  select datediff(day, LoginDate, getdate()) as DD, 
         LoginTime,
         sum(ClickEvents) as ClickEvents
  from YourTable
  where LoginDate >= dateadd(day, -7, cast(getdate() as date))
  group by LoginDate, LoginTime
), H as
(
  select 1 as LoginTime
  union all
  select H.LoginTime+1
  from H
  where H.LoginTime < 24
)  
select H.LoginTime, 
       sum(case DD when 1 then C.ClickEvents else 0 end) as Day1,
       sum(case DD when 2 then C.ClickEvents else 0 end) as Day2,
       sum(case DD when 3 then C.ClickEvents else 0 end) as Day3,
       sum(case DD when 4 then C.ClickEvents else 0 end) as Day4,
       sum(case DD when 5 then C.ClickEvents else 0 end) as Day5,
       sum(case DD when 6 then C.ClickEvents else 0 end) as Day6,
       sum(case DD when 7 then C.ClickEvents else 0 end) as Day7
from H
  left outer join C
    on C.LoginTime = H.LoginTime
group by H.LoginTime
order by H.LoginTime;

在SE Data 上试用

如果您使用MS SQL，您可以使用以下内容：

SELECT COUNT(*) FROM SecurityLog WHERE DATEPART(hh, LOGINDate) = 1 AND LOGINDate = [somedate]
SELECT COUNT(*) FROM SecurityLog WHERE DATEPART(hh, LOGINDate) = 2 AND LOGINDate = [somedate]
....
....
SELECT COUNT(*) FROM SecurityLog WHERE DATEPART(hh, LOGINDate) = 24 AND LOGINDate = [somedate]

这将告诉您安全日志中有多少记录与任何给定时间、任何给定日期相匹配。只需将[somedate]替换为有问题的日期即可。

参考：http://msdn.microsoft.com/en-us/library/ms174420.aspxhttp://syntaxhelp.com/SQLServer/Breaking-a-date-by-hour-into-24-parts

这不是一个完整的解决方案。我刚刚提供了一个想法，希望在此基础上您可以构建您的解决方案。创建一个表来存储工时信息怎么样？比如说，1,2…24。然后你可以做这样的事情：

SELECT
    h.[Hour],
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 1 AND GetDate()      THEN  1 ELSE 0 END) as DAY1,
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 2 AND GetDate() - 1  THEN  1 ELSE 0 END) as DAY2,
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 3 AND GetDate() - 2  THEN  1 ELSE 0 END) as DAY3, 
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 4 AND GetDate() - 3  THEN  1 ELSE 0 END) as DAY4, 
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 5 AND GetDate() - 4  THEN  1 ELSE 0 END) as DAY5, 
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 6 AND GetDate() - 5  THEN  1 ELSE 0 END) as DAY6, 
    SUM(CASE WHEN  t.LOGINDate BETWEEN GetDate() - 7 AND GetDate() - 6  THEN  1 ELSE 0 END) as DAY7 
FROM 
    tblHour h LEFT JOIN SecurityLog t 
    ON h.[Hour] = DATEPART(hour, t.LOGINDate)
WHERE 
    t.LOGINDate >= CONVERT(datetime, CONVERT(varchar, getdate() -7, 102)) AND
    t.LOGINDate < DATEADD(day, 1, CONVERT(datetime, CONVERT(varchar, getdate(), 102)))
GROUP BY 
    t.LOGINDate, h.[Hour]