找到小于1的记录

hongyuan20022003 2008-04-03 07:35:31

表格如下：

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的记录，以及下一记录（按时间），没有就留空白。

...全文

92 点赞收藏 11

写回复

11 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

flairsky 2008-04-03

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

回复赞

wangxuelid 2008-04-03

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

回复赞

wangxuelid 2008-04-03



/*

01       0.8	 10:01	55.0	 10:21	

02       0.7	 9:17		

03       0.1	 10:22	3.0	 11:00	

*/

回复赞

wangxuelid 2008-04-03



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

回复赞

fa_ge 2008-04-03

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

回复赞

fa_ge 2008-04-03



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 行）

*/

回复赞

pt1314917 2008-04-03



--上面有点问题：

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

回复赞

让你望见影子的墙 2008-04-03

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

回复赞

bqb 2008-04-03

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

回复赞

dawugui 2008-04-03

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

*/

回复赞

pt1314917 2008-04-03





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