select t1.*,t2.response
from (select a.p_nbr,max(b.t_nbr) as t_nbr from ta a,tb b where a.p_nbr=b.p_nbr and a.t_nbr>b.t_nbr group by a.p_nbr ) t1
left join tb t2 on t1.p_nbr=t2.p_nbr and t1.t_nbr=t2.t_nbr
select t1.*,t2.response
from (select a.p_nbr,max(b.t_nbr) as t_nbr from ta a,tb b where a.p_nbr=b.p_nbr and a.t_nbr>b.t_nbr group by a.p_nbr ) t1
left join tb t2 on t1.p_nbr=t2.p_nbr and t1.t_nbr=t2.t_nbr
--建立環境
create table ta (p_nbr varchar(20), t_nbr int, fulltext varchar(20))
insert into ta
select '12345' , 7 , 'XXX' create table tb (p_nbr varchar(20), t_nbr int, response varchar(20))
insert into tb
select '12345', 1 , 'a' union allselect '12345', 5 , 'b' union allselect '12345' , 8 , 'c' select t1.*,t2.response
from (select a.p_nbr,max(b.t_nbr) as t_nbr from ta a,tb b where a.p_nbr=b.p_nbr and a.t_nbr>b.t_nbr group by a.p_nbr ) t1
left join tb t2 on t1.p_nbr=t2.p_nbr and t1.t_nbr=t2.t_nbr
/*結果
p_nbr t_nbr response
--------------------------------------------
12345 5 b
*/
create table ta(
p_nbr int,
t_nbr smallint,
fulltext varchar(20)
)
gocreate table tb (
p_nbr int,
t_nbr smallint,
response varchar(10)
)
goinsert into ta
select 12345,3,'XXX' union all
select 12345,6,'XXX' union all
select 12345,7,'XXX'
select 12345,9,'XXX'
goinsert tb
select 12345,1,'a'
select 12345,5,'b'
select 12345,8,'c'
goselect * into #tmp1 from (
select p_nbr,t_nbr,'check' as response from ta union all
select p_nbr,t_nbr,response from tb) cselect identity(int,1,1) id, * into #tmp2 from #tmp1 order by t_nbr
--select * from #tmp2select T1.response from #tmp2 T1 inner join (
select a.id, max(a.id) mid from #tmp2 a inner join #tmp2 b on a.id < b.id where b.response = 'check' and a.response <> 'check' group by a.id
) T2 on T1.id = T2.id
----------- ------ --------------------
12345 7 XXX
12345 3 XXX
12345 6 XXX
12345 9 XXXp_nbr t_nbr response
----------- ------ ----------
12345 1 a
12345 5 b
12345 8 cresponse
----------
a
b
c