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

 
-- 测试数据
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 行受影响)

*/