我有一个SQL表,其中一列(dateRec)包含日期,格式为:yyyy-mm-dd.
在SQL中是否有一种方法可以定义日期范围,然后按这些范围对所有项目进行分组?我在这里需要以下小组:
第一组= 0 - 7天
第二组= 8 - 14天
第三组= 15 - 30日龄
第四组= 31 - 60天
第五组=休息
我的标准查询来获取该表中的所有项:
CREATE PROCEDURE [dbo].[FetchRequests] AS BEGIN SET NOCOUNT ON; SELECT subject, dateRec, category FROM LogRequests WHERE logStatus = 'active' ORDER BY dateRec desc, subject FOR XML PATH('items'), ELEMENTS, TYPE, ROOT('ranks') END
蒂姆,谢谢你的帮助.
你需要做这样的事情
select t.range as [score range], count(*) as [number of occurences] from ( select case when score between 0 and 9 then ' 0-9 ' when score between 10 and 19 then '10-19' when score between 20 and 29 then '20-29' ... else '90-99' end as range from scores) t group by t.range
检查此链接在SQL中,如何在范围内"分组"?
是的,您可以通过添加一个新列来实现这一点,该列包含您需要的所有波段,然后按该列分组:
SELECT subject, dateRec, category ,case when datediff(day,dateRec,Getdate())<='7' then '0 - 7 days old' when datediff(day,dateRec,Getdate()) between '8' and '14' then '8 - 14 days old' when datediff(day,dateRec,Getdate()) >60 then 'rest' end Classes into #temp1 FROM LogRequests WHERE logStatus = 'active' ORDER BY dateRec desc, subject
我错过了你的几个范围,但希望你有逻辑
然后按此栏分组:
select classes, Count(*) from #temp1 begin drop table #temp1 end