关于分组连续日期合并为一起

最新推荐文章于 2025-08-11 15:39:12 发布

原创最新推荐文章于 2025-08-11 15:39:12 发布 · 597 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#date #table #insert #function #测试

t_sql 程序设计_function 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种使用SQL函数来合并连续日期的方法，并展示了如何通过自定义函数实现这一目标。此外，还提供了处理不连续日期的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--测试数据
create table t(name varchar(10),date smalldatetime)
insert into t
select 'a','2008-01-01'
union all
select 'a','2008-01-02'
union all
select 'a','2008-01-03'
union all
select 'a','2008-01-06'
union all
select 'a','2008-01-07'
union all
select 'a','2008-01-09'
union all
select 'b','2008-01-01'
union all
select 'b','2008-01-02'
union all
select 'b','2008-01-03'
union all
select 'b','2008-01-04'
union all
select 'b','2008-01-07'
union all
select 'b','2008-01-08'

--1要求分组合并连续的日期最大和最小
--2 单独显示不连续的日期

--徐王锦 2008/12/30 pm 16:40--

--2005
create function f_4(@name varchar(10))returns @table table(name varchar(10),date varchar(4000))
as
begin
declare @minpx int,@maxpx int,@date smalldatetime,@date2 smalldatetime
declare @table2 table(name varchar(10),date smalldatetime,px int)
insert into @table2
select name,date,px=row_number()over(partition by name order by date)
   from t
    where name=@name
select @minpx=2,@maxpx=@@rowcount
select @date=date
from @table2
   where px=1
insert into @table select @name,convert(char(10),@date,120)
select @date2=date
from @table2
   where px=@minpx
while @minpx<=@maxpx
begin
if @date2=(select cast(right(date,10) as smalldatetime)+1 from @table where cast(right(date,10) as smalldatetime)=@date)
begin
   update a set a.date=a.date+N'-'+convert(char(10),@date2,120)
    from @table as a where cast(right(a.date,10) as smalldatetime)=@date
end
else if @date2>(select cast(right(date,10) as smalldatetime)+1 from @table where cast(right(date,10) as smalldatetime)=@date)
begin
   insert into @table select @name,convert(char(10),@date2,120)
end
select @date=@date2,@minpx=@minpx+1
select @date2=date from @table2 where px=@minpx
end
return
end

select
x2.name,
case when len(x2.date)>10 then left(x2.date,10)+N'-'+right(x2.date,10) else x2.date end as date
from
(select distinct name from t)x
cross apply
f_4(x.name) as x2

/*
name       date
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a          2008-01-01-2008-01-03
a          2008-01-06-2008-01-07
a          2008-01-09
b          2008-01-01-2008-01-04
b          2008-01-07-2008-01-08

(5 行受影响)

*/