找到小于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

回复
学习下。难度在与下一条记录如何寻找
回复
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

回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-03 07:35
社区公告
暂无公告