MySQL WHERE <multiple-column> IN <subquery>
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11393423/
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
MySQL WHERE <multiple-column> IN <subquery>
提问by Max13
Is there a way (without JOIN) to use the WHEREclause on 2 columns (OR) INa subquery?
Currently, I'm doing
有没有办法（没有 JOIN）WHERE在 2 列（OR）IN子查询上使用子句？目前，我正在做
WHERE 'col1' IN
(
SELECT id FROM table
) OR 'col2' IN
(
SELECT id FROM table
)
And I'm sure I can do better :) . i've also tried WHERE ('col1', 'col2') IN <subquery>but MySQL says: Operand should contain 2 column(s)
而且我相信我可以做得更好 :) 。我也试过，WHERE ('col1', 'col2') IN <subquery>但 MySQL 说：Operand should contain 2 column(s)
Thanks for your help.
谢谢你的帮助。
Edit: By "No join", I mean I'm alreeady making many joins: http://pastebin.com/bRfD21W9, and as you can see, the subqueries are on another table.
编辑：通过“无连接”，我的意思是我已经进行了很多连接：http://pastebin.com/bRfD21W9 ，正如您所看到的，子查询在另一个表上。
采纳答案by manurajhada
I Read you are not agree with JOIN, but just another way to do it.. See joinwith friendsif it is useful to you..
我读你不同意JOIN，只是另一种方式来做到这一点。请参见join与friends它是否对你有用..
SELECT `timeline`.`action`, `timeline`.`data`, `timeline`.`tlupdate`,
u1.`id` AS ufrom_id, u1.`username` AS ufrom_username, u1.`firstname` AS ufrom_firstname, u1.`lastname` AS ufrom_lastname, u1.`picture` AS ufrom_picture,
u2.`id` AS uto_id, u2.`username` AS uto_username, u2.`firstname` AS uto_firstname, u2.`lastname` AS uto_lastname, u2.`picture` AS uto_picture,
m.`id` AS m_id, m.`name` AS m_name, m.`alternative_name` AS m_altname, m.`tiny_img` AS m_tiny, m.`normal_img` AS m_normal
FROM `timeline`
JOIN `users` u1 ON u1.`id` = `timeline`.`user_id_from`
JOIN `users` u2 ON u2.`id` = `timeline`.`user_id_to`
JOIN `friends` f on f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`
JOIN `movies` m ON m.`id` = `timeline`.`movie_id`;
Update:
更新：
As you are using inner joinYou can this too to avoid the condition on complete resultSet.
当您使用时，inner join您也可以这样做以避免出现完整结果集的情况。
JOIN `friends` f on ((f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`) and f.idol_id = ?)
Either you can use DISTINCTor use GROUP BYto get unique result.
您可以使用DISTINCT或使用GROUP BY来获得独特的结果。
回答by Avinash Saini
SELECT *
FROM table
WHERE
(col_1, col_2) NOT IN
(SELECT col_1, col_2 FROM table)
回答by Matin Kh
Rewrite your code like this and you're good to go:
像这样重写你的代码，你就可以开始了：
WHERE ('col1', 'col2') IN
(
SELECT id, id FROM table
)
回答by MosheElisha
manurajhada answer is good. If you still decide to avoid the JOIN you could:
manurajhada 的回答很好。如果您仍然决定避免 JOIN，您可以：
SELECT ... FROM (SELECT id FROM table) subquery, other_table
WHERE other_table.col1 = subquery.id OR other_table.col2 = subquery.id
This should make MySQL use the join buffer to hold the results of the subquery.
这应该使 MySQL 使用连接缓冲区来保存子查询的结果。
回答by sameer.nuna
I also face similar problem
我也面临类似的问题
SELECT COUNT(*) FROM msg_old WHERE idfrom IN
(SELECT olduserid FROM temp_user)
OR
idto IN (SELECT olduserid FROM temp_user)
I came up with solution
我想出了解决方案
SELECT * FROM msg_old
INNER JOIN temp_user
ON msg_old.IDFROM = temp_user.olduserid
OR msg_old.IDTO = temp_user.olduserid
回答by metalfight - user868766
You can try below query:
您可以尝试以下查询：
select name from user_details where groupid IN ( select g_id from user_groups ) OR cityid IN ( select c_id from user_cities );
Hope this help.
希望这有帮助。
回答by sunlight
yes, you can do. It is easy enough. you must select same number of columns in sub query as you mentioned in the where clause.
是的，你可以。这很容易。您必须在子查询中选择与 where 子句中提到的相同数量的列。
