sql 按天及上午下午分组

select  convert(nvarchar(10),r.CTime,120),case when DATEPART(hh,r.CTime)/12=0 then '上午' when DATEPART(hh,r.CTime)/12=1 then '下午' end as k, count(*) 
from Record  as r left join Channel as c on r.ChannelId=c.Id 
where r.MissionId='b62c4373-0738-45df-854a-53d843ebcf64' and r.CTime >='2018/06/01' 
group by convert(nvarchar(10),r.CTime,120),DATEPART(hh,r.CTime)/12
order by convert(nvarchar(10),r.CTime,120) asc,k asc

 关键点

convert(nvarchar(10),r.CTime,120)//返回2018-06-01(长度10)

DATEPART(hh,r.CTime)/12 //取小时，然后除12，对值进行分组

 

转载于:https://www.cnblogs.com/Qos8/p/9234502.html