SQL Server：按范围对日期进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20330596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: group dates by ranges
提问by user2571510
I have an SQL table with one column (dateRec) containing dates, format: yyyy-mm-dd.
我有一个 SQL 表，其中一列 (dateRec) 包含日期，格式：yyyy-mm-dd。
Is there a way in SQL that I can define date ranges and then group all the items by these ranges ? I would need the following groups here:
有没有办法在 SQL 中定义日期范围，然后按这些范围对所有项目进行分组？我需要以下组：
- group one = 0 - 7 days old
- group two = 8 - 14 days old
- group three = 15 - 30 days old
- group four = 31 - 60 days old
- group five = rest
- 第一组 = 0 - 7 天
- 第二组 = 8 - 14 天
- 第三组 = 15 - 30 天
- 第四组 = 31 - 60 天
- 第五组=休息
My standard query to fetch all items from that table:
我从该表中获取所有项目的标准查询：
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
Thanks for any help with this, Tim.
感谢您对此的任何帮助，蒂姆。
采纳答案by Nitin Varpe
You need to do something like this
你需要做这样的事情
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
Check this link In SQL, how can you "group by" in ranges?
检查此链接在 SQL 中，如何在范围内“分组”？
回答by Kiril Rusev
Yes, you can do that by adding a new column which contains all the bands you require and then group by that column:
是的，您可以通过添加一个包含您需要的所有波段的新列然后按该列分组来实现：
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
I have missed couple of your ranges, but hopefully you got the logic
我错过了你的几个范围，但希望你明白逻辑
then Group by this column:
然后按此列分组：
select classes,
Count(*)
from #temp1
begin drop table #temp1 end
回答by Anon
WITH ranges AS (
SELECT
range_id,
ISNULL(DATEADD(day,range_start,CAST(GETDATE() AS date)),'0000-01-01') range_start,
ISNULL(DATEADD(day,range_end ,CAST(GETDATE() AS date)),'9999-12-31') range_end
FROM (VALUES
(1, -7, 0),
(2, -14, -8),
(3, -30,-15),
(4, -60,-31),
(5,NULL,-61)
) r(range_id,range_start,range_end)
)
SELECT subject,
range_id,
category
COUNT(*) AS c,
FROM LogRequests
WHERE logStatus = 'active'
INNER JOIN ranges ON dateRec BETWEEN range_start AND range_end
GROUP BY subject,category,range_id
ORDER BY range_id desc, subject
