alter table tb add Fid int identity(1,1)GoDelete a From tb a inner join tb as b on a.outtime=b.outtime And a.intime = b.intime and a.fid>b.fidalter table tb drop column fid
select outtime,min(intime) as intime from test group by outtime
to pooplin(枫叶): 我想要的是 outtime,intime中时间 如果一条记录为2005-5-1,2005-5-3 那么就不能有2005-5-2,XXXXXXXX 因为两条时间记录存在交叉 2005-5-2在2005-5-1和2005-5-3之间了
--生成数据 create table #t(outtime datetime,intime datetime) insert into #t select '2005-5-1','2005-5-2' insert into #t select '2005-5-2','2005-5-3' insert into #t select '2005-5-4','2005-5-5' insert into #t select '2005-5-1','2005-5-3' insert into #t select '2005-5-2','2005-5-3' insert into #t select '2005-5-4','2005-5-6' --执行查询 select distinct a.* from #t a where not exists(select 1 from #t where outtime = a.outtime and intime < a.intime)--输出结果 outtime intime ---------- ---------- 2005-05-01 2005-05-02 2005-05-02 2005-05-03 2005-05-04 2005-05-05
--生成数据 create table #t(outtime datetime,intime datetime) insert into #t select '2005-5-1','2005-5-2' insert into #t select '2005-5-2','2005-5-3' insert into #t select '2005-5-4','2005-5-5' insert into #t select '2005-5-1','2005-5-3' insert into #t select '2005-5-2','2005-5-3' insert into #t select '2005-5-4','2005-5-6' --执行查询 select distinct a.* from #t a where not exists(select 1 from #t where outtime between a.outtime and a.intime and intime between a.outtime and a.intime and (outtime !=a .outtime or intime != a.intime))--输出结果 outtime intime ----------------------- ----------------------- 2005-05-01 00:00:00.000 2005-05-02 00:00:00.000 2005-05-02 00:00:00.000 2005-05-03 00:00:00.000 2005-05-04 00:00:00.000 2005-05-05 00:00:00.000
select outtime,min(intime) as intime from test group by outtime
我想要的是 outtime,intime中时间
如果一条记录为2005-5-1,2005-5-3
那么就不能有2005-5-2,XXXXXXXX
因为两条时间记录存在交叉 2005-5-2在2005-5-1和2005-5-3之间了
create table #t(outtime datetime,intime datetime)
insert into #t select '2005-5-1','2005-5-2'
insert into #t select '2005-5-2','2005-5-3'
insert into #t select '2005-5-4','2005-5-5'
insert into #t select '2005-5-1','2005-5-3'
insert into #t select '2005-5-2','2005-5-3'
insert into #t select '2005-5-4','2005-5-6'
--执行查询
select
distinct a.*
from
#t a
where
not exists(select
1
from
#t
where
outtime = a.outtime
and
intime < a.intime)--输出结果
outtime intime
---------- ----------
2005-05-01 2005-05-02
2005-05-02 2005-05-03
2005-05-04 2005-05-05
create table #t(outtime datetime,intime datetime)
insert into #t select '2005-5-1','2005-5-2'
insert into #t select '2005-5-2','2005-5-3'
insert into #t select '2005-5-4','2005-5-5'
insert into #t select '2005-5-1','2005-5-3'
insert into #t select '2005-5-2','2005-5-3'
insert into #t select '2005-5-4','2005-5-6'
--执行查询
select
distinct a.*
from
#t a
where
not exists(select
1
from
#t
where
outtime between a.outtime and a.intime
and
intime between a.outtime and a.intime
and
(outtime !=a .outtime or intime != a.intime))--输出结果
outtime intime
----------------------- -----------------------
2005-05-01 00:00:00.000 2005-05-02 00:00:00.000
2005-05-02 00:00:00.000 2005-05-03 00:00:00.000
2005-05-04 00:00:00.000 2005-05-05 00:00:00.000