sql server查询每小时数据去重个数(group by时间,distinct另一列?)

心态要好 心态要好 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
素材:
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影响,有其他写法更好
...全文

相关推荐

评论 6
收藏 已收藏
分享
返回顶部
心态要好
疑难问题 22,299 成员 121,727 内容