在SQL中使用左抗加入

发布于 2025-02-04 00:35:57 字数 1577 浏览 4 评论 0原文

我有一个会话表，例如（Table1）：

会话ID	发送者ID	日期
S1	S1 S1	2020-10-02
S2	S1	S2 S1 2020-10-06
S3	S2 S2	2020-03-01
S4	S4 S2	2020-03-03-02
S3	事件	2020-08--08- 02
S6	S4	2020-02-02

和交易表（Table2）类似：

交易ID	发件人ID	发送日期
T1	S1 S1	2020-10-01
T2	S1	2020-10-05
T3	S2 S2	2020-04-04-01
T4	S3	2020-07-07--07--07--07--07--07--07- 02
T5	S4	2020-12-12

我想生成一个表，该表仅包含在会话日期之前未进行任何交易的用户的会话。根据上表，我想返回：

会话ID	发件人ID	事件日期
S3	S2	2020-03-01
S4	S2	2020-03-02
S6 S6	S4	2020-02-02

我想使用反JOIN解决此问题。以下代码为此目的有效吗？

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.sender_id = t1.sender_id 
    AND t2.event_date > t1.event_date
WHERE t2.sender_id IS NULL

请随时提出除反加入以外的任何方法。谢谢！

原文

I have a session table like (table1):

session ID	sender ID	event date
s1	s1	2020-10-02
s2	s1	2020-10-06
s3	s2	2020-03-01
s4	s2	2020-03-02
s5	s3	2020-08-02
s6	s4	2020-02-02

And a transactions table (table2) like:

transaction ID	sender ID	send date
t1	s1	2020-10-01
t2	s1	2020-10-05
t3	s2	2020-04-01
t4	s3	2020-07-02
t5	s4	2020-12-12

I want to generate a table that only contains the sessions of the users who have not made any transaction before their session date. According to the above tables, I want to return:

session ID	sender ID	event date
s3	s2	2020-03-01
s4	s2	2020-03-02
s6	s4	2020-02-02

I want to solve this using Anti-Join. Would the below code work for this purpose?

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.sender_id = t1.sender_id 
    AND t2.event_date > t1.event_date
WHERE t2.sender_id IS NULL

Please feel free to suggest any method other than anti-join.
Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小情绪 2025-02-11 00:35:57

您可以使用存在如：

select *
from t1
where not exists (
  select 1 
  from t2 
  where t2.sender_id = t1.sender_id and t2.send_date < t1.event_date
)

You can use EXISTS as in:

select *
from t1
where not exists (
  select 1 
  from t2 
  where t2.sender_id = t1.sender_id and t2.send_date < t1.event_date
)

回复收藏 0 原文

深巷少女 2025-02-11 00:35:57

除外，条款除外，我们还可以达到相同的结果：

select * from sessiontbl
except
select t1.session_id,t1.sender_id,t1.event_date
from sessiontbl t1 inner join transactiontbl t2
on t1.sender_id = t2.sender_id
where t1.event_date > t2.send_date

With except clause, we can also achieve the same result:

select * from sessiontbl
except
select t1.session_id,t1.sender_id,t1.event_date
from sessiontbl t1 inner join transactiontbl t2
on t1.sender_id = t2.sender_id
where t1.event_date > t2.send_date

回复收藏 0 原文

~没有更多了~