所以我有一个名为users_chat_rooms的表:
mysql> describe users_chat_rooms;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| chat_id | int(11) unsigned | NO | MUL | NULL | |
+---------+------------------+------+-----+---------+----------------+
我有两个用户具有独特的user_id,我正在尝试弄清楚如何找出他们是否有/chat_id的共同点。
谢谢!
我会像这样加入:
select ucr1.chat_id from users_chat_room ucr1
inner join users_chat_room ucr2 on ucr1.chat_id = ucr2.chat_id
where ucr1.user_id = :userid1 and ucr2.user_id = :userid2
这样的查询应该可以解决问题:
select chat_id
from users_chat_rooms
where user_id in (#USERID1#, #USERID2#)
group by chat_id
having count(distinct user_id) = 2