日期:2014-05-17 浏览次数:20548 次
create table cn
(id int,status int,date varchar(16),price int)
insert into cn
select 1, 1, '2013-10-01', 218 union all
select 2, 1, '2013-10-02', 218 union all
select 3, 0, '2013-10-03', 218 union all
select 4, 0, '2013-10-04', 238 union all
select 5, 0, '2013-10-05', 238 union all
select 6, 0, '2013-10-06', 238 union all
select 7, 0, '2013-10-07', 258 union all
select 8, 0, '2013-10-08', 258 union all
select 9, 0, '2013-10-09', 218
-- 查询1
with t as
(select a.id,a.price,
row_number() over(order by a.id) 'rn'
from cn a
left join cn b on a.id=b.id+1
where b.id is null or a.price<>b.price)
select d.mindate+'至'+d.maxdate 'date',c.price
from
(select a.id 'x',isnull(b.id,a.id) 'y',a.price
from t a
left join t b on a.rn=b.rn-1) c
cross apply
(select min(e.[date]) 'mindate',max(e.[date]) 'maxdate'
from cn e
where e.id between c.x and c.y and e.price=c.price) d
/*
date price
---------------------------------- -----------
2