有个两个表连接问题，请教各位

有A,B两张表
A表结构
actionid (PK)
time(int)
a_ts_user(int)


B表结构：
id(PK)，
starttime
endtime
b_ts_user

想做连接

B表中b_ts_user,不是主键，所有会重复出现多个b_ts_user,并且对应的starttime,endtime都不一样。

而A表中的a_ts_user不会出现重复的

连接条件是：
A.a_ts_user=B.b_ts_user

希望连接的时候，仅仅是A每个action的time是落在B的starttime以及endtime之间，才可以连接起来。如果
A.a_ts_user=B.b_ts_user成立情况下，time会落在多个多个【starttime,endtime】之间，那么仅仅连接B中最大id那个记录

比如
A:
100 10 jim
200 11 tom
300 12 john

B：
1 9 12 jim
2 9 13 jim

3 5 8 jim
4 11 13 john

那么就选出

100 10 2 9 13 jim
300 12 4 11 13 john

jim有多个记录候选，1，2都是满足时间的，3不是满足时间的，但是选合适中id最大的2记录。

请大家帮帮忙

SQL code

select * from A表 a
left join B表 b
on A.a_ts_user=B.b_ts_user
and a.time between b.starttime and b.endtime

SQL code

select * from A m,B n  where m.a_ts_user=n.b_ts_user
and m.time between n.starttime and n.endtime
and not exists(select 1 from B where b_ts_user=n.b_ts_user and id>n.id)

但是你的结果jim会出现连个啊
B中1，2都出现

怎么再次接触上再挑出最大id那个呢

就是仅仅连接第二个记录

这个好像没有连接啊（inner，left，right什么的）
not exists能保证连接的是最大id那个吗

SQL code

declare @A table (actionid int,time int,a_ts_user varchar(4))
insert into @A
select 100,10,'jim' union all
select 200,11,'tom' union all
select 300,12,'john'

declare @B table 
(id int,starttime int,endtime int,b_ts_user varchar(4))
insert into @B
select 1,9,12,'jim' union all
select 2,9,13,'jim' union all
select 3,5,8,'jim' union all
select 4,11,13,'john'

;with maco as(
select * from @A a left join @B b
on a.a_ts_user=b.b_ts_user
and a.time between b.starttime and b.endtime
where b.id is not null
)
select a.actionid,a.time,id,starttime,endtime,b_ts_user from maco a where
id=(select max(id) from maco where actionid=a.actionid)
order by actionid
/*
actionid    time        id          starttime   endtime     b_ts_user
----------- ----------- ----------- ----------- ----------- ---------
100         10          2           9           13          jim
300         12          4           11          13          john
*/

SQL code

select * from A m,B n  where m.a_ts_user=n.b_ts_user
and not exists(select 1 from B where b_ts_user=n.b_ts_user 
          and m.time between starttime and endtime and id>n.id)

SQL code

declare @A table (actionid int,time int,a_ts_user varchar(4))
insert into @A
select 100,10,'jim' union all
select 200,11,'tom' union all
select 300,12,'john'

declare @B table 
(id int,starttime int,endtime int,b_ts_user varchar(4))
insert into @B
select 1,9,12,'jim' union all
select 2,9,13,'jim' union all
select 3,5,8,'jim' union all
select 4,11,13,'john'

select * from @A m,@B n  where m.a_ts_user=n.b_ts_user
and m.time between n.starttime and n.endtime
and not exists(select 1 from @B where b_ts_user=n.b_ts_user 
          and m.time between starttime and endtime and id>n.id)

/*
actionid    time        a_ts_user id          starttime   endtime     b_ts_user
----------- ----------- --------- ----------- ----------- ----------- ---------
100         10          jim       2           9           13          jim
300         12          john      4           11          13          john

maco,zy112429两位太负责了。感谢两位
maco的意思我看懂了，先通过CTE，求出所有满足的，然后对每个actioid都挑出max的，如果仅有1个，那么max就是本身了，我说的对吗


zy112429:很感谢，但是我没看懂not exist那部分。。。求解释。

SQL code

create table t1(actionid int,time int,a_ts_user varchar(10))
insert into t1 select 100,10,'jim'
insert into t1 select 200,11,'tom'
insert into t1 select 300,12,'john'
create table t2(id int,starttime int,endtime int,b_ts_user varchar(10))
insert into t2 select 1,9,12,'jim'
insert into t2 select 2,9,13,'jim'
insert into t2 select 3,5,8,'jim'
insert into t2 select 4,11,13,'john'
go
select a.actionid,a.time,b.id,b.starttime,b.endtime,a.a_ts_user
from t1 a inner join t2 b on a.a_ts_user=b.b_ts_user
where a.time between b.starttime and b.endtime
 and not exists(select 1 from t2 where b_ts_user=b.b_ts_user and a.time between starttime and endtime and id>b.id)
go
drop table t1,t2
/*
actionid    time        id          starttime   endtime     a_ts_user
----------- ----------- ----------- ----------- ----------- ----------
100         10          2           9           13          jim
300         12          4           11          13          john

(2 行受影响)

*/

and not exists(select 1 from t2 where b_ts_user=b.b_ts_user and a.time between starttime and endtime and id>b.id)
的意思是:找出t2中满足条件的那几个记录中的最大的那个.(满足条件是前面两个判断,最后id>b.id的意思是,没有更大的,不就是最大的了嘛).