select id=identity(int,0,1),date into #t from 表select a.date,b.date
from #t a join #t b on a.id+1=b.id
where a.id%2=0 and b.id%2=1drop table #t
from #t a join #t b on a.id+1=b.id
where a.id%2=0 and b.id%2=1drop table #t
from #t a join #t b on a.id+1=b.id
where a.id%2=0 and b.id%2=1drop table #t
create table 表(date char(10),status bit)
insert 表 select '2004-04-01',1
union all select '2004-04-02',1
union all select '2004-04-03',0
union all select '2004-04-04',1
union all select '2004-04-05',1
union all select '2004-04-06',1
go--查询
select id=identity(int,0,1),date into #t from 表 where status=1select a.date,b.date
from #t a join #t b on a.id+1=b.id
where a.id%2=0 and b.id%2=1drop table #t
go--删除测试
drop table 表/*--测试结果
date date
---------- ----------
2004-04-01 2004-04-02
2004-04-04 2004-04-05
(所影响的行数为 2 行)
--*/
a1.date=convert(varchar(10),cast(b1.date as datetime)-1,120))or date=(select max(date)from 表 where status=1)))as date1 from 表 a where status>(select status from 表 b where
a.date=convert(varchar(10),cast(b.date as datetime)+1,120)) or date=(select min(date)from 表 where status=1)2004-04-01 2004-04-02
2004-04-04 2004-04-06
insert 表 select '2004-04-01',1
union all select '2004-04-02',1
union all select '2004-04-03',0
union all select '2004-04-04',1
union all select '2004-04-05',1
union all select '2004-04-06',1select identity(int,1,1) as id, [date] into # from 表 where status=1
select a.date,b.date
from (select (id+1)/2 id, date from # where (id+1)/2<>(1+id)*1.0/2) a,(select (1+id)/2 id,date from # where (id+1)/2=(1+id)*1.0/2) b
where a.id=b.iddrop table 表,#
date date
---------- ----------
2004-04-02 2004-04-01
2004-04-05 2004-04-04
(select min(date)
from 表 b
where
status = a.status
and date >= isnull((select min(date)
from 表
where date <= a.date
and status <> a.status
),0) ) ,(select max(date)
from 表
where status = a.status
and date <= isnull((select min(date)
from 表
where date >= a.date
and status <> a.status
),'9999-12-31')
) ,status
from 表 a
where status >0
--查询
select date,date1=(
select min(date) from 表 aa
where status=1 and date>=a.date
and not exists(
select 1 from 表 where status=1
and datediff(day,aa.date,date)=1
))
from 表 a
where status=1 and not exists(
select 1 from 表 where status=1
and datediff(day,date,a.date)=1)
create table 表(date char(10),status bit)
insert 表 select '2004-04-01',1
union all select '2004-04-02',1
union all select '2004-04-03',0
union all select '2004-04-04',1
union all select '2004-04-05',1
union all select '2004-04-06',1
union all select '2004-04-07',0
union all select '2004-04-08',1
union all select '2004-04-09',0
union all select '2004-04-10',1
union all select '2004-04-11',1
go--查询
select date,date1=(
select min(date) from 表 aa
where status=1 and date>=a.date
and not exists(
select 1 from 表 where status=1
and datediff(day,aa.date,date)=1
))
from 表 a
where status=1 and not exists(
select 1 from 表 where status=1
and datediff(day,date,a.date)=1)
go--删除测试
drop table 表/*--测试结果
date date1
---------- ----------
2004-04-01 2004-04-02
2004-04-04 2004-04-06
2004-04-08 2004-04-08
2004-04-10 2004-04-11(所影响的行数为 4 行)
--*/
,max(date)
,min(cast(status as int))
from
(SELECT *,(select count(*)
from 表
where date > a.date
and status <> a.status) as id
from 表 a) T
group by id,cast(status as int)
having min(cast(status as int)) <> 0
order by min(date)select distinct
(select min(date)
from 表 b
where
status = a.status
and date >= isnull((select min(date)
from 表
where date <= a.date
and status <> a.status
),0) ) ,(select max(date)
from 表
where status = a.status
and date <= isnull((select min(date)
from 表
where date >= a.date
and status <> a.status
),'9999-12-31')
) ,status
from 表 a
where status > 0
所以还想问一下,假如status字段并不是bit型的,而是任意数字,那么能不能实现把数字相同的日期范围列出呢?啊,其实就是不但把1的范围列出来,同时把0的范围也列出
,max(date)
,min(cast(status as int))
from
(SELECT *,(select count(*)
from 表
where date > a.date
and status <> a.status) as id
from 表 a) T
group by id,cast(status as int)order by min(date)