22,301
社区成员




select cmac,InTime from Interface_SurfingLog_WP_SnifferData
SELECT CONVERT(VARCHAR(10),[InTime],120)[日期\小时],
SUM(CASE WHEN DATEPART(HH,[InTime])=1 THEN 1 END)[1],
SUM(CASE WHEN DATEPART(HH,[InTime])=2 THEN 1 END)[2],
SUM(CASE WHEN DATEPART(HH,[InTime])=3 THEN 1 END)[3],
SUM(CASE WHEN DATEPART(HH,[InTime])=4 THEN 1 END)[4],
SUM(CASE WHEN DATEPART(HH,[InTime])=5 THEN 1 END)[5],
SUM(CASE WHEN DATEPART(HH,[InTime])=6 THEN 1 END)[6],
SUM(CASE WHEN DATEPART(HH,[InTime])=7 THEN 1 END)[7],
SUM(CASE WHEN DATEPART(HH,[InTime])=8 THEN 1 END)[8],
SUM(CASE WHEN DATEPART(HH,[InTime])=9 THEN 1 END)[9],
SUM(CASE WHEN DATEPART(HH,[InTime])=10 THEN 1 END)[10],
SUM(CASE WHEN DATEPART(HH,[InTime])=11 THEN 1 END)[11],
SUM(CASE WHEN DATEPART(HH,[InTime])=12 THEN 1 END)[12],
SUM(CASE WHEN DATEPART(HH,[InTime])=13 THEN 1 END)[13],
SUM(CASE WHEN DATEPART(HH,[InTime])=14 THEN 1 END)[14],
SUM(CASE WHEN DATEPART(HH,[InTime])=15 THEN 1 END)[15],
SUM(CASE WHEN DATEPART(HH,[InTime])=16 THEN 1 END)[16],
SUM(CASE WHEN DATEPART(HH,[InTime])=17 THEN 1 END)[17],
SUM(CASE WHEN DATEPART(HH,[InTime])=18 THEN 1 END)[18],
SUM(CASE WHEN DATEPART(HH,[InTime])=19 THEN 1 END)[19],
SUM(CASE WHEN DATEPART(HH,[InTime])=20 THEN 1 END)[20],
SUM(CASE WHEN DATEPART(HH,[InTime])=21 THEN 1 END)[21],
SUM(CASE WHEN DATEPART(HH,[InTime])=22 THEN 1 END)[22],
SUM(CASE WHEN DATEPART(HH,[InTime])=23 THEN 1 END)[23],
SUM(CASE WHEN DATEPART(HH,[InTime])=24 THEN 1 END)[24]
FROM[Interface_SurfingLog_WP_SnifferData]GROUP BY CONVERT(VARCHAR(10),[InTime],120)
ORDER BY[日期\小时]DESC
select * from (select convert(varchar(10), InTime,120) as [日期\小时],datepart(hh,InTime) [time],count(ID)sc
from [Interface_SurfingLog_WP_SnifferData] group by convert(varchar(10), InTime,120),datepart(hh,InTime))t pivot (sum(sc)
for time in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]))a order by [日期\小时] desc
这种写法其实效率更高,count(ID)可以替换成别的列,例:count(distinct(cmac))COUNT(DISTINCT(CASE WHEN DATEPART(HH,[InTime])=1 THEN cmac END))[1],