34,838
社区成员




/*
01 0.8 10:01 55.0 10:21
02 0.7 9:17
03 0.1 10:22 3.0 11:00
*/
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
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 行)
*/
--上面有点问题:
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
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
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
*/
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