mysql - 与带有查询的 MySQL 的交集

Question

我试图以这种方式在 MySQL 中交叉两个查询:

SELECT user_id FROM post GROUP BY thread_id HAVING COUNT(thread_id) = 1
INTERSECT
SELECT user_id FROM post GROUP BY user_id HAVING COUNT(user_id) = 1;

我一直在寻找在 MySQL 中执行此操作的方法，但不幸的是，使用 IN 和 INNER JOIN 对我不起作用。

我正在使用的测试数据是:

insert into post (post_number, timestamp, user_id, thread_id) values(0,0,1,1000);
insert into post (post_number, timestamp, user_id, thread_id) values(0,0,2,2000);
insert into post (post_number, timestamp, user_id, thread_id) values(0,0,3,2000);
insert into post (post_number, timestamp, user_id, thread_id) values(0,0,4,3000);
insert into post (post_number, timestamp, user_id, thread_id) values(0,0,5,4000);
insert into post (post_number, timestamp, user_id, thread_id) values(0,0,1,4000);

运行我的两个查询的交集应该导致

4

感谢任何帮助，谢谢。

Answer 1

在 postgres 中我会这样做

SELECT user_id FROM post where thread_id in (
select thread_id from post group by thread_id having count(distinct user_id) = 1)
INTERSECT
SELECT user_id FROM post GROUP BY user_id HAVING COUNT(user_id) = 1;

然而，显然 mysql 不支持相交，因此内部连接就足够了:

SELECT post.user_id FROM post 
join (SELECT user_id FROM post GROUP BY user_id HAVING COUNT(user_id) = 1) x on x.user_id = post.user_id
where 
post.thread_id in (
select thread_id from post group by thread_id having count(distinct user_id) = 1)

那些都返回 4