找出唯一的id

Question

表格如下：id num time
a   0    9:01
a   1    9:02
a   1    9:03
a   1    9:04
a   1    9:05
a   1    9:06
a   1    9:07
a   1    9:08
b   1    9:01
b   1    9:02
b   1    9:03
b   1    9:04
b   1    9:05
b   0    9:06
b   1    9:07
b   1    9:08
c   1    9:01
c   1    9:02
c   0    9:03
c   1    9:04
c   1    9:05
c   0    9:06
c   1    9:07
c   1    9:08..  ..    ...希望找出 某段时间内 全是1 的id，而且一定保证是唯一的一个。 比如  a 9:02 9:08 全是1,而且 没有其他id 在9:02 9:08 也是如此。
所以 找出 a 9:02 9:08这个区间。2000系统，

Answer 1

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-11-04 22:24:12
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
go 
create table [test](
[id] varchar(1),
[num] int,
[time] time
)
insert [test]
select 'a',0,'9:01' union all
select 'a',1,'9:02' union all
select 'a',1,'9:03' union all
select 'a',1,'9:04' union all
select 'a',1,'9:05' union all
select 'a',1,'9:06' union all
select 'a',1,'9:07' union all
select 'a',1,'9:08' union all
select 'b',1,'9:01' union all
select 'b',1,'9:02' union all
select 'b',1,'9:03' union all
select 'b',1,'9:04' union all
select 'b',1,'9:05' union all
select 'b',0,'9:06' union all
select 'b',1,'9:07' union all
select 'b',1,'9:08' union all
select 'c',1,'9:01' union all
select 'c',1,'9:02' union all
select 'c',0,'9:03' union all
select 'c',1,'9:04' union all
select 'c',1,'9:05' union all
select 'c',0,'9:06' union all
select 'c',1,'9:07' union all
select 'c',1,'9:08'
with 
t as(
select
[id],
[num],
[time]
from 
test a
where 
a.[time] between '9:02' and '9:08'
)
select
*
from 
t a
where
not exists(
select 1 from t b where a.id=b.id and a.num<>b.num
)
and a.num=1
----------------结果----------------------------
/* id   num         time
---- ----------- ----------------
a    1           09:02:00.0000000
a    1           09:03:00.0000000
a    1           09:04:00.0000000
a    1           09:05:00.0000000
a    1           09:06:00.0000000
a    1           09:07:00.0000000
a    1           09:08:00.0000000(7 行受影响)*/

Answer 2

create table test_id1
(
id varchar(10),
num int,
time datetime 
)
GO
select * from test_id1
delete from test_id1
insert into test_id1 (id,num,time)
select 'a',0,'9:01'union all
select 'a',1,'9:02'union all
select 'a',1,'9:03'union all
select 'a',1,'9:04'union all
select 'a',1,'9:05'union all
select 'a',1,'9:06'union all
select 'a',1,'9:07'union all
select 'a',1,'9:08'union all
select 'b',1,'9:01'union all
select 'b',1,'9:02'union all
select 'b',1,'9:03'union all
select 'b',1,'9:04'union all
select 'b',1,'9:05'union all
select 'b',0,'9:06'union all
select 'b',1,'9:07'union all
select 'b',1,'9:08'union all
select 'c',1,'9:01'union all
select 'c',1,'9:02'union all
select 'c',0,'9:03'union all
select 'c',1,'9:04'union all
select 'c',1,'9:05'union all
select 'c',0,'9:06'union all
select 'c',1,'9:07'union all
select 'c',1,'9:08'select 
rep.id,convert(varchar(100),Min(time),108),convert(varchar(100),Max(time),108)
from 
(select id,time from test_id1 where num=1) as rep
group by 
rep.id
order by id

Answer 3

id        StartTime  EndTime
--------- --------- ---------
a         09:02:00   09:08:00
b         09:01:00   09:08:00
c         09:01:00   09:08:00(3 行受影响)

Answer 4

2000,没有time数据类型,不能用CTE.
楼主的需求,b 的 9:01~9:05 也是唯一的.
create table tb(id varchar(1), num int, t varchar(10))
insert tb
select 'a',0,'9:01' union all
select 'a',1,'9:02' union all
select 'a',1,'9:03' union all
select 'a',1,'9:04' union all
select 'a',1,'9:05' union all
select 'a',1,'9:06' union all
select 'a',1,'9:07' union all
select 'a',1,'9:08' union all
select 'b',1,'9:01' union all
select 'b',1,'9:02' union all
select 'b',1,'9:03' union all
select 'b',1,'9:04' union all
select 'b',1,'9:05' union all
select 'b',0,'9:06' union all
select 'b',1,'9:07' union all
select 'b',1,'9:08' union all
select 'c',1,'9:01' union all
select 'c',1,'9:02' union all
select 'c',0,'9:03' union all
select 'c',1,'9:04' union all
select 'c',1,'9:05' union all
select 'c',0,'9:06' union all
select 'c',1,'9:07' union all
select 'c',1,'9:08'  
go
select a.id,a.t t1,b.t t2 into # from(
select id,t from tb a where num=1 and not exists(select 1 from tb where id=a.id and t<a.t)
union all
select a.id,a.t from tb a inner join tb b on a.id=b.id and a.t>b.t
where a.num=1 and b.num=0 and not exists(select 1 from tb where id=a.id and num=1 and t<a.t and t>b.t)
)a inner join(
select id,t from tb a where num=1 and not exists(select 1 from tb where id=a.id and t>a.t)
union all
select a.id,a.t from tb a inner join tb b on a.id=b.id and a.t<b.t
where a.num=1 and b.num=0 and not exists(select 1 from tb where id=a.id and num=1 and t>a.t and t<b.t)
)b on a.id=b.id and a.t<b.t and not exists(select 1 from tb where id=a.id and num=0 and t>a.t and t<b.t)select id,t1,t2 from # a where not exists(select 1 from # where id<>a.id and t1<=a.t1 and t2>=a.t2)
/*
id   t1         t2
---- ---------- ----------
b    9:01       9:05
a    9:02       9:08(2 行受影响)*/
go
drop table tb,#

Answer 5

如果仅求一个最大值,则:
create table tb(id varchar(1), num int, t varchar(10))
insert tb
select 'a',0,'9:01' union all
select 'a',1,'9:02' union all
select 'a',1,'9:03' union all
select 'a',1,'9:04' union all
select 'a',1,'9:05' union all
select 'a',1,'9:06' union all
select 'a',1,'9:07' union all
select 'a',1,'9:08' union all
select 'b',1,'9:01' union all
select 'b',1,'9:02' union all
select 'b',1,'9:03' union all
select 'b',1,'9:04' union all
select 'b',1,'9:05' union all
select 'b',0,'9:06' union all
select 'b',1,'9:07' union all
select 'b',1,'9:08' union all
select 'c',1,'9:01' union all
select 'c',1,'9:02' union all
select 'c',0,'9:03' union all
select 'c',1,'9:04' union all
select 'c',1,'9:05' union all
select 'c',0,'9:06' union all
select 'c',1,'9:07' union all
select 'c',1,'9:08'  
go--找出连续t为1的区间
select a.id,a.t t1,b.t t2 into # from(
--找出各id num为1的最小t值
select id,t from tb a where num=1 and not exists(select 1 from tb where id=a.id and t<a.t)
union all
--找出各id num为0后面 num 为1的t值
select a.id,a.t from tb a inner join tb b on a.id=b.id and a.t>b.t
where a.num=1 and b.num=0 and not exists(select 1 from tb where id=a.id and num=1 and t<a.t and t>b.t)
)a inner join(
--找出各id num为1的最大t值
select id,t from tb a where num=1 and not exists(select 1 from tb where id=a.id and t>a.t)
union all
--找出各id num为0前面num为1的最大t值
select a.id,a.t from tb a inner join tb b on a.id=b.id and a.t<b.t
where a.num=1 and b.num=0 and not exists(select 1 from tb where id=a.id and num=1 and t>a.t and t<b.t)
)b on a.id=b.id and a.t<b.t and not exists(select 1 from tb where id=a.id and num=0 and t>a.t and t<b.t)select id,t1,t2 from # a 
where not exists(select 1 from # where datediff(n,convert(datetime,t1),convert(datetime,t2))>datediff(n,convert(datetime,a.t1),convert(datetime,a.t2)))
/*
id   t1         t2
---- ---------- ----------
a    9:02       9:08(1 行受影响)*/
go
drop table tb,#