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