找到小于1的记录

[hongyuan20022003]

表格如下：

id num time
01 22 9:01
01 1 9:44
01 0.8 10:01
01 55 10:21
02 1 9:03
02 0.7 9:17
03 22 10:17
03 0.1 10:22
03 3 11:00
希望得到如下结果

id n1 t1 n2 t2
01 0.8 10:01 55 10:21
02 0.7 9:17
03 0.1 10:22 3 11:00

找到小于1的记录，以及下一记录（按时间），没有就留空白。

[pt1314917]

declare @t table(id varchar(10),num money,[time] datetime)
insert into @t select '01',22,'9:01'
insert into @t select '01',1,'9:44'
insert into @t select '01',0.8,'10:01'
insert into @t select '01',55,'10:21'
insert into @t select '02',1,'9:03'
insert into @t select '02',0.7,'9:17'
insert into @t select '03',22,'10:17'
insert into @t select '03',0.1,'10:22'
insert into @t select '03',3,'11:00'

select m.*,n.num,n.time from 
@t m 
left join
(select b.* from @t a,@t b where a.num<1 and b.id=a.id and b.time>a.time)n
on m.id=n.id 
where m.num<1

[dawugui]

create table tb(id varchar(10) , num decimal(18,1) , time varchar(10))
insert into tb values('01', 22  , '9:01') 
insert into tb values('01',  1  , '9:44') 
insert into tb values('01', 0.8 , '10:01') 
insert into tb values('01', 55  , '10:21') 
insert into tb values('02', 1   , '9:03') 
insert into tb values('02', 0.7 , '9:17') 
insert into tb values('03', 22  , '10:17') 
insert into tb values('03', 0.1 , '10:22') 
insert into tb values('03', 3   , '11:00') 
go

select m.id , m.num n1 , m.time t1 , n.num n2 , m.t2 from
(
  select * , t2 = replace(cast((select min(cast(replace(time,':','.') as decimal(18,2))) from tb where id = t.id and cast(replace(time,':','.') as decimal(18,2)) > cast(replace(t.time,':','.') as decimal(18,2))) as varchar),'.',':') from tb t where num < 1
) m
left join tb n
on m.id = n.id and m.t2 = n.time

drop table tb

/*
id         n1                                      t1         n2                                      t2
---------- --------------------------------------- ---------- --------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01         0.8                                     10:01      55.0                                    10:21
02         0.7                                     9:17       NULL                                    NULL
03         0.1                                     10:22      3.0                                     11:00
*/

[bqb]

create table tb(id char(2),num numeric(6,1), time char(5))
insert into tb
select '01',22,'9:01' union all 
select '01',1,'9:44' union all 
select '01',0.8,'10:01' union all 
select '01',55,'10:21' union all  
select '02',1,'9:03' union all  
select '02',0.7,'9:17' union all  
select '03',22,'10:17' union all  
select '03',0.1,'10:22' union all  
select '03',3,'11:00' 

 
 

select 
t1.id,
t1.num as t1,
t1.time as t1,
isnull(ltrim(t2.num),'') as n2,
isnull(t2.time,'') as t2 from 
(select *,(select count(1) from tb where id=a.id and right('0'+time,5)<= right('0'+a.time,5) ) cid from tb a where num<1) t1
left join 
(select *,(select count(1) from tb where id=a.id and  right('0'+time,5)<= right('0'+a.time,5) ) cid from tb a ) t2 on t1.id=t2.id and t1.cid=t2.cid-1

/*
id	n1	t1	n2	t2
-----------------------------------------
01	.8	10:01	55.0	10:21
02	.7	09:17		     
03	.1	10:22	3.0	11:00
*/


drop table tb
 

[让你望见影子的墙]

学习下。难度在与下一条记录如何寻找

[pt1314917]

--上面有点问题：
declare @t table(id varchar(10),num money,[time] varchar(10))
insert into @t select '01',22,'9:01'
insert into @t select '01',1,'9:44'
insert into @t select '01',0.8,'10:01'
insert into @t select '01',55,'10:21'
insert into @t select '02',1,'9:03'
insert into @t select '02',0.7,'9:17'
insert into @t select '03',22,'10:17'
insert into @t select '03',0.1,'10:22'
insert into @t select '03',3,'11:00'


select m.*,n.num,n.time from 
@t m 
left join
(select a.* from @t a,@t b
where b.num<1 and b.id=a.id and a.time>b.time 
and not exists(select 1 from @t where a.id=id and time<a.time and time>b.time))n
on m.id=n.id 
where m.num<1

[fa_ge]

declare @t table(id1 int identity(1,1),id varchar(10),num money,[time] varchar(10))
insert into @t select '01',22,'9:01'
insert into @t select '01',1,'9:44'
insert into @t select '01',0.8,'10:01'
insert into @t select '01',55,'10:21'
insert into @t select '02',1,'9:03'
insert into @t select '02',0.7,'9:17'
insert into @t select '03',22,'10:17'
insert into @t select '03',0.1,'10:22'
insert into @t select '03',3,'11:00'

--增加一个identity id1
select 
	id,num,time,
	num1=(select  top 1 num  from @t where id=a.id and id1>a.id1),
	time1=(select top 1 time from @t where id=a.id and id1>a.id1)
from @t a
where num<1

/*
id         num                   time       num1                  time1      
---------- --------------------- ---------- --------------------- ---------- 
01         .8000                 10:01      55.0000               10:21
02         .7000                 9:17       NULL                  NULL
03         .1000                 10:22      3.0000                11:00

（所影响的行数为 3 行）
*/

[fa_ge]

如果这个表中的 time 是按每个id 顺序排列的话，我上面可以不要 增加 id1,替换下就好了

[wangxuelid]

Create  table tb (id varchar(10),num numeric(18,1),[time] datetime)

insert into tb select '01',22,'9:01'
insert into tb select '01',1,'9:44'
insert into tb select '01',0.8,'10:01'
insert into tb select '01',55,'10:21'
insert into tb select '02',1,'9:03'
insert into tb select '02',0.7,'9:17'
insert into tb select '03',22,'10:17'
insert into tb select '03',0.1,'10:22'
insert into tb select '03',3,'11:00'

select m.id,m.num,substring(convert(varchar(20) ,m.time),11,6),isnull(Convert(varchar(20),n.num),''),isnull(convert(varchar(20),substring(convert(varchar(20) , n.time),11,6)),'')
 from tb m 
left join
(select b.* from tb a,tb b where a.num<1 and b.id=a.id and b.time>a.time)n
on m.id=n.id 
where m.num<1

[wangxuelid]

/*
01       0.8	 10:01	55.0	 10:21	
02       0.7	 9:17		
03       0.1	 10:22	3.0	 11:00	
*/

[wangxuelid]

[Quote=引用 7 楼 fa_ge 的回复:]
如果这个表中的 time 是按每个id 顺序排列的话，我上面可以不要 增加 id1,替换下就好了
[/Quote]
你说的方法的确好一点，

[flairsky]

select m.*,n.num,n.time from
@t m
left join
(select b.* from @t a,@t b where a.num<1 and b.id=a.id and b.time>a.time)n
on m.id=n.id
where m.num<1