MySQL 使用单个查询选择和删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10491316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select and Delete with a single query
提问by Baylock
I have 2 tables:
我有2张桌子：
- users (user_id, user_connected)
- rooms (room_id, room_initiating_user_id, room_target_user_id)
- 用户（user_id，user_connected）
- 房间（room_id、room_initiating_user_id、room_target_user_id）
I would like to delete all the "rooms" having both the initiating user and the target_user set to "user_connected=0"
我想删除所有将发起用户和 target_user 设置为“user_connected=0”的“房间”
I have two problems here:
我在这里有两个问题：
How to target these users? Obviously this query won't work:
SELECT room_id FROM rooms,users WHERE ( (room_target_user_id=user_id) AND (user_connected=0) ) AND ( (room_initiating_user_id=user_id) AND (user_connected=0) )I would like, with the same query if possible, to delete these rooms (no problem if I use a second query but it means that this query would be triggered for each result, which is a lot. Isn't it possible to delete these rooms at once?
如何定位这些用户？显然这个查询不起作用：
SELECT room_id FROM rooms,users WHERE ( (room_target_user_id=user_id) AND (user_connected=0) ) AND ( (room_initiating_user_id=user_id) AND (user_connected=0) )如果可能的话，我想用相同的查询删除这些房间（如果我使用第二个查询没问题，但这意味着每个结果都会触发这个查询，这是很多。是否可以删除这些？房间一次？
回答by Lajos Arpad
delete from rooms
where room_initiating_user_id in (select user_id from users where user_connected = 0)
and room_target_user_id in (select user_id from users where user_connected = 0)
回答by bfavaretto
To target the users, you need to JOINtable userstwice, once for the initiating user, and another one for the target user:
要定位用户，您需要JOIN表users两次，一次用于发起用户，另一次用于目标用户：
SELECT room.room_id
FROM rooms room
INNER JOIN users initiating
ON room.room_initiating_user_id = initiating.user_id
INNER JOIN users target
ON room.room_target_user_id = target.user_id
WHERE initiating.user_connected=0 AND target.user_connected=0
To delete those rooms, you can use the above as a subquery:
要删除这些房间，您可以使用上述作为子查询：
DELETE FROM rooms
WHERE room_id IN (
SELECT room.room_id
FROM rooms room
INNER JOIN users initiating
ON room.room_initiating_user_id = initiating.user_id
INNER JOIN users target
ON room.room_target_user_id = target.user_id
WHERE initiating.user_connected=0 AND target.user_connected=0
)
回答by Ezequiel Muns
First, to select these rooms, you'll need to join to the users table twice:
首先，要选择这些房间，您需要两次加入 users 表：
SELECT r.room_id
FROM rooms r
JOIN users tgt ON r.room_target_user_id = tgt.user_id
JOIN users ini ON r.room_initiating_user_id = ini.user_id
WHERE tgt.user_connected = 0 AND ini.user_connected = 0
Note: The use of two distinct aliases for the table users, so as to differentiate the two joins. Also, I' using JOIN syntax, rather than the older comma joins which are discouraged
注意：表users 使用两个不同的别名，以便区分这两个join。此外，我使用 JOIN 语法，而不是不鼓励使用的旧逗号连接
To delete the rooms, you'll need a multi-table delete:
要删除房间，您需要进行多表删除：
DELETE r
FROM rooms r
JOIN users tgt ON r.room_target_user_id = tgt.user_id
JOIN users ini ON r.room_initiating_user_id = ini.user_id
WHERE tgt.user_connected = 0 AND ini.user_connected = 0
