create table ta(p_nbr varchar(10), t_nbr int, fulltext varchar(10))
insert into ta values('12345', 2, 'XXX')
insert into ta values('12345', 7, 'XXX')
insert into ta values('45678', 1, 'XXX')
create table tb(p_nbr varchar(10), t_nbr int, response varchar(10))
insert into tb values('12345', 1, 'a' )
insert into tb values('12345', 5, 'b' )
insert into tb values('12345', 8, 'c' )
insert into tb values('45678', 1, 'a' )
insert into tb values('45678', 3, 'd' )
goselect a.p_nbr,a.t_nbr,b.response from ta a,
(
select m.p_nbr , m.t_nbr t_nbr1, isnull(n.t_nbr,m.t_nbr+1) t_nbr2, m.response from
(select px=(select count(1) from tb where p_nbr=a.p_nbr and t_nbr<a.t_nbr)+1 , * from tb a) m left join
(select px=(select count(1) from tb where p_nbr=a.p_nbr and t_nbr<a.t_nbr)+1 , * from tb a) n
on m.p_nbr = n.p_nbr and m.px = n.px - 1
) b
where a.p_nbr = b.p_nbr and a.t_nbr >= b.t_nbr1 and a.t_nbr < b.t_nbr2drop table ta,tb/*
p_nbr t_nbr response
---------- ----------- ----------
12345 2 a
12345 7 b
45678 1 a(3 行受影响)
*/
insert into ta values('12345', 2, 'XXX')
insert into ta values('12345', 7, 'XXX')
insert into ta values('45678', 1, 'XXX')
create table tb(p_nbr varchar(10), t_nbr int, response varchar(10))
insert into tb values('12345', 1, 'a' )
insert into tb values('12345', 5, 'b' )
insert into tb values('12345', 8, 'c' )
insert into tb values('45678', 1, 'a' )
insert into tb values('45678', 3, 'd' )
goselect a.p_nbr,a.t_nbr,b.response from ta a,
(
select m.p_nbr , m.t_nbr t_nbr1, isnull(n.t_nbr,m.t_nbr+1) t_nbr2, m.response from
(select px=(select count(1) from tb where p_nbr=a.p_nbr and t_nbr<a.t_nbr)+1 , * from tb a) m left join
(select px=(select count(1) from tb where p_nbr=a.p_nbr and t_nbr<a.t_nbr)+1 , * from tb a) n
on m.p_nbr = n.p_nbr and m.px = n.px - 1
) b
where a.p_nbr = b.p_nbr and a.t_nbr >= b.t_nbr1 and a.t_nbr < b.t_nbr2drop table ta,tb/*
p_nbr t_nbr response
---------- ----------- ----------
12345 2 a
12345 7 b
45678 1 a(3 行受影响)
*/
create table DoNoPoA
(
p_nbr varchar(10),
t_nbr int,
fulltext varchar(10)
)create table DoNoPoB
(
p_nbr varchar(10),
t_nbr int,
response varchar(10)
)
delete DoNoPoB
delete DoNoPoA
insert into DoNoPoA
select '12345' , 2 , 'XXX' union all select '12345' , 7 , ' XXX' union all select
'12345' , 9 , ' XXX' union all select '45678' , 1 , 'XXX'
insert into DoNoPoB
select '12345' , 1 , 'a' union all select'12345' , 5 , 'b' union all select'12345' , 8 , 'c' union all select
'12345' , 12 , 'D' union all select'45678' , 1 , 'a' union all select'45678' , 3 , ' d' select A.p_nbr ,A.t_nbr, B.response
from (
select A.p_nbr,A.t_nbr,max(B.t_nbr) as t_nbrB
from DoNoPoA A left join DoNoPoB B
on A.p_nbr= B.p_nbr where A.t_nbr > = B.t_nbr
group by A.p_nbr ,A.t_nbr ) A
left join
DoNoPoB B
on A.p_nbr = B.p_nbr where A.t_nbrB= B.t_nbr
/*
-------------------
p_nbr t_nbr response
12345 2 a
12345 7 b
12345 9 c
45678 1 a
*/
/*
先求出B 表中 小于 A 表 的 t_nbr
*/
select A.p_nbr,A.t_nbr, B.t_nbr
from DoNoPoA A left join DoNoPoB B
on A.p_nbr= B.p_nbr where A.t_nbr > = B.t_nbr
/*
再通过 group by A.p_nbr ,A.t_nbr 求出 , B 表中 小于 A 表 的 t_nbr 的最大的t_nbr
*/
select A.p_nbr,A.t_nbr,max(B.t_nbr) as t_nbrB
from DoNoPoA A left join DoNoPoB B
on A.p_nbr= B.p_nbr where A.t_nbr > = B.t_nbr
group by A.p_nbr ,A.t_nbr
/*
再次通过 和 B 表 连接 求出 response
*/
select A.p_nbr ,A.t_nbr, B.response
from (
select A.p_nbr,A.t_nbr,max(B.t_nbr) as t_nbrB
from DoNoPoA A left join DoNoPoB B
on A.p_nbr= B.p_nbr where A.t_nbr > = B.t_nbr
group by A.p_nbr ,A.t_nbr ) A
left join
DoNoPoB B
on A.p_nbr = B.p_nbr where A.t_nbrB= B.t_nbr