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

问题见

http://blog.youkuaiyun.com/wlzd3636/archive/2008/12/31/3670162.aspx

 

关于分组连续日期合并为一起的优化
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'

 

--徐王锦 2009/01/20 pm 16:11
--1
create index index_t on t(date)
--2
select name,
       case when min(date)=max(date)
             then convert(char(10),min(date),120)
            else convert(char(10),min(date),120)+N'-'+convert(char(10),max(date),120)
       end as date
from
(select name,date,date-row_number()over(partition by name order by date) as px
from t)x
group by name,px
order by name
--2万行数据测试
--机器配置 cpu pm1.8  内存 1g
用函数
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
耗时 01:54

用
select name,
       case when min(date)=max(date)
             then convert(char(10),min(date),120)
            else convert(char(10),min(date),120)+N'-'+convert(char(10),max(date),120)
       end as date
from
(select name,date,date-row_number()over(partition by name order by date) as px
from t)x
group by name,px
order by name
耗时
00:00