考虑我有下表:
+----+--------+--------+
| id | roomID | userID |
+----+--------+--------+
| 1 | 9 | 34 |
| 2 | 9 | 76 |
| 3 | 87 | 90 |
+----+--------+--------+
我想找到只有某些用户参与的室友 - 因此,在这种情况下,我想找到仅使用34和76使用的RoomID。我该怎么做?
我想,您在问一些不同的事情,不是吗?这就是您可以找到用户34
和76
的roomID
的方式:
select
roomID
from
table
where
userID in (34,76)
您可以使用以下查询:
select roomID from your_table_name where Userid in (34,76)
有关唯一数据,您可以使用:
select roomID from your_table_name where Userid in (34,76) group by roomID
找到仅用户34和76的室友正在使用
SELECT
roomID
FROM
TABLE1
WHERE
roomID IN (SELECT roomID FROM TABLE1 WHERE userID = 76)
AND
roomID IN (SELECT roomID FROM TABLE1 WHERE userID = 34)
AND
roomID NOT IN (SELECT roomID FROM TABLE1 WHERE userID NOT IN (34, 76))
您可以使用: in
SELECT roomID FROM table_name WHERE userID IN (34, 76);