我需要一些代码方面的帮助
我有一个名为"stuff"的数据库表，我有以下信息：
+------+-------------+---------------------+
| id | member_id | group_id |
+------+-------------+---------------------+
| 1 | 11 | aa |
+------+-------------+---------------------+
| 2 | 11 | bb |
+------+-------------+---------------------+
| 3 | 22 | bb |
+------+-------------+---------------------+
| 4 | 11 | cc |
+------+-------------+---------------------+
| 5 | 22 | cc |
+------+-------------+---------------------+
| 6 | 33 | cc |
+------+-------------+---------------------+
我需要什么：
If I search for group_id when member_id = 11 the result will be aa
If I search for group_id when member_id = 11 AND member_id = 22 the result will be bb
If I search for group_id when member_id = 11 AND member_id = 22 AND member_id = 33 the result will be cc
SELECT group_id
FROM stuff
GROUP BY group_id
HAVING SUM(member_id = 11) = 1
AND SUM(member_id = 22) = 1
AND COUNT(*) = 2
- SQLFiddle 演示
另一种变体，
SELECT group_id
FROM stuff a
WHERE member_id IN (11,22,33) -- <<== list of member_id
AND
EXISTS
(
SELECT group_id
FROM stuff b
WHERE a.group_id = b.group_id
GROUP BY group_id
HAVING COUNT(*) = 3 -- <<== number of member_id
)
GROUP BY group_id
HAVING COUNT(*) = 3 -- <<== number of member_id
- SQLFiddle 演示
您可以在 select 语句中使用IN()运算符：
SELECT * FROM stuff
WHERE member_id IN(11,22,33);
在前端，您可以连接值或用逗号分隔member_id。
请注意，member_id不能同时是 11 和 22。你是说或吗？以下 2 个查询将始终返回 null：
search for group_id when member_id = 11 AND member_id = 22
search for group_id when member_id = 11 AND member_id = 22 AND member_id = 33