sqlserver 每个月连续五天的日期为一组 有什么办法列出来吗？

Question

sqlserver 每个月连续五天的日期为一组 有什么办法列出来吗？

Answer 1

使用 row_number 排出序号，然后序号减一后，比5取整，即可得到分组序号

select number,(ROW_NUMBER() over(order by number)-1)/5 as group_sn from master..spt_values where type='p'

至于每个月连续5天这个问题，你需要自行补全日期

declare @sd date,@ed date
set @sd='2021-1-1'
set @ed=GETDATE()
;with t as (
    select number,DATEADD(d,number,@sd) as dt 
    from master..spt_values
    where type='p' and number<=DATEDIFF(D,@sd,@ed)
),t1 as (
    select *,(ROW_NUMBER() over(partition by convert(varchar(6),dt,112) order by dt)-1)/5 as group_sn,convert(varchar(6),dt,112) as mon 
    from t
) 
select * from t1

还可以行转列后看的更清晰

declare @sd date,@ed date
set @sd='2021-1-1'
set @ed=GETDATE()
;with t as (
    select number,DATEADD(d,number,@sd) as dt 
    from master..spt_values
    where type='p' and number<=DATEDIFF(D,@sd,@ed)
),t1 as (
    select dt,(ROW_NUMBER() over(partition by convert(varchar(6),dt,112) order by dt)-1)/5 as group_sn,convert(varchar(6),dt,112) as mon 
    from t
) 
select mon,'第'+convert(varchar,group_sn+1)+'个五天',[1] as 第一天日期,[2] as 第二天日期,[3] as 第三天日期,[4] as 第四天日期,[5] as 第五天日期 
from (
    select *,ROW_NUMBER() over(partition by group_sn,mon order by dt) as sn from t1
) a
pivot(max(dt) for sn in ([1],[2],[3],[4],[5])) p