我正在尝试检查数据库是否存在特定组合.
表：对话
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
表：conversations_users
+----+--------------+------+
| id | conversation | user |
+----+--------------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 3 | 2 |
| 8 | 3 | 3 |
| 9 | 4 | 2 |
| 10 | 4 | 4 |
+----+--------------+------+
Users: 1,2,3,4 (Only them,no else)
如果存在只有那些会话的对话,我想获得该对话的id,否则结果应该变为0
任何人都有任何想法如何做到这一点？
最佳答案
这是“set-within-sets”查询的示例.对于这些,我喜欢使用group by with having子句：
原文链接：https://www.f2er.com/mysql/434284.htmlselect conversation
from conversation_users cu
group by conversation
having SUM(user = 1) > 0 and
sum(user = 2) > 0 and
sum(user = 3) > 0 and
sum(user = 4) > 0 and
sum(user not in (1,4)) = 0
having子句的每个条件对应于问题中指定的五个条件之一：