sql server查询每小时数据去重个数（group by时间，distinct另一列？）

[心态要好]

素材：

select cmac,InTime from Interface_SurfingLog_WP_SnifferData

现在要查询每个小时cmac个数，这个是不去重的：

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

求教如何统计个数的时候根据cmac去重，去重后查询结果2015-09-23 14点应该是2个不是3个，有重复的cmac
不要受我提供的sql影响，有其他写法更好

[qq_17482963]

group by后面加上cmac不就行了

[心态要好]

引用 1 楼 qq_17482963 的回复:

group by后面加上cmac不就行了

感谢回复！ 试过了，加上后一天就出来多条记录。 出来的结果就要上面截图的格式，每天就一行数据，显示不同时间段的去重cmac个数

[等不到来世]

COUNT(DISTINCT(CASE WHEN DATEPART(HH,[InTime])=1 THEN cmac END))[1],

[心态要好]

引用 3 楼 szx1999 的回复:

COUNT(DISTINCT(CASE WHEN DATEPART(HH,[InTime])=1 THEN cmac END))[1],

感谢兄弟，能查出结果了，就是不知道到服务器上效率是否可行。分都给你吧

[心态要好]

引用 3 楼 szx1999 的回复:

COUNT(DISTINCT(CASE WHEN DATEPART(HH,[InTime])=1 THEN cmac END))[1],

分错加给1L了，其实3L才是正确答案，希望搜索引擎带过来的人知道。
已联系版主，不知道能不能补加给你

[心态要好]

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))