我正在努力编写一个查询以获取与指定用户相关的线程。
我在下面包含了一些虚拟数据和我的表格架构,以及带有一些边缘情况的预期输出。
任何帮助都将不胜感激。
用户
+----+--------+
| id | name |
+----+--------+
| 1 | User 1 |
| 2 | User 2 |
| 3 | User 3 |
+----+--------+
线程
+------+----------+
| id | title |
+------+----------+
| 1 | Thread 1 |
| 2 | Thread 2 |
| 3 | Thread 3 |
+------+----------+
thread_user
+-----------+---------+
| thread_id | user_id |
+-----------+---------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
+-----------+---------+
伪查询
select all from threads where related user id's are exactly 1 and 2
预期输出
+------+----------+
| id | title |
+------+----------+
| 2 | Thread 2 |
+------+----------+
Thread 2
是在结果列表中,因为它仅与User 1
和User 2
有关
Thread 1
不是在结果列表中,因为它仅与 User 1
和不是 User 2
。
Thread 3
不是在结果列表中,因为它与User 1
和User 2
和 User 3
。
编辑以处理案例3以及
select t.id, t.title
from users u
inner join thread_user tu on tu.user_id = u.id
inner join threads t on t.id = tu.thread_id
inner join (select thread_id
from thread_user
group by thread_id
having count(*) = 2) sq on sq.thread_id = t.id
where u.id in (1,2)
group by t.id, t.title
having count(*) = 2
我不在我的开发环境中,所以无法检查语法正确性,但是类似的东西应该起作用,
select t.id, t.title
from users u
inner join thread_user tu on tu.user_id = u.id
inner join threads t on t.id = tu.thread_id
where u.id in (1,2)
group by t.id, t.title
having count(*) = 2