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

Question

有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     johnB：
1  9   12    jim
2  9   13    jim3  5    8    jim
4  11   13   john那么就选出100 10 2 9 13 jim
300 12  4 11 13 johnjim有多个记录候选，1，2都是满足时间的，3不是满足时间的，但是选合适中id最大的2记录。

Answer 1

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

Answer 2

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)

Answer 3

但是你的结果jim会出现连个啊
B中1，2都出现怎么再次接触上再挑出最大id那个呢就是仅仅连接第二个记录

Answer 4

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

Answer 5

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
*/

Answer 6

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)

Answer 7

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

Answer 8

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

Answer 9

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 行受影响)*/

Answer 10

 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的意思是,没有更大的,不就是最大的了嘛).

Answer 11

to qianjin036a: 所以就相当于是表B的自连接，是吗？

Answer 12

与自己比较， not exists 表示不存在后面子查询里面查找的数据