php 带有双选择语句的嵌套 mysql 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11540065/
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
nested mysql query with double select statement?
提问by Marco
I have a simple problem regarding a nested query. To be honest I don't know if it can be done through one query only or if I'll have to use PHP.
我有一个关于嵌套查询的简单问题。老实说，我不知道是否只能通过一个查询来完成，或者我是否必须使用 PHP。
Put simply, I want to return the users information from the users tables by the users IDs returned from a select statement in relations table.
简而言之，我想通过从关系表中的选择语句返回的用户 ID 返回用户表中的用户信息。
I can do this by 2 queries and some PHP loop but for saving resources, but I think it's better to combine it into 1 query and single loop.
我可以通过 2 个查询和一些 PHP 循环来做到这一点，但为了节省资源，但我认为最好将它组合成 1 个查询和单个循环。
First query
第一次查询
SELECT UserID FROM relations WHERE GroupID = '1'
Second query I need to retrieve the user info from the user table by the returned UsersIDs from the first select statement.
第二个查询我需要通过从第一个 select 语句返回的 UsersID 从用户表中检索用户信息。
I can do this by loop through the ID and making the queries but I think I can get all in 1 query.
我可以通过循环遍历 ID 并进行查询来做到这一点，但我认为我可以在 1 个查询中获得全部。
Thanks
谢谢
回答by Roland Bouman
This is the typical way to do that:
这是执行此操作的典型方法：
SELECT users.*
FROM users
INNER JOIN relations
ON users.id = relations.userid
WHERE relations.groupid = 1
I noticed you were using quotes around the 1 in your query. I am assuming group id is an integer value, in which case you should not use quotes.
我注意到您在查询中的 1 周围使用了引号。我假设 group id 是一个整数值，在这种情况下你不应该使用引号。
The answers that use an IN subquery are likely to be less performant. Esp. with MySQL, the JOIN should always we the preferred way to combine results from tables, since MySQL has particularly lackluster subquery implementation.
使用 IN 子查询的答案可能性能较差。特别是 对于 MySQL，JOIN 应该始终是我们从表中组合结果的首选方式，因为 MySQL 的子查询实现特别乏味。
回答by bugwheels94
Select * from user_table where id in(SELECT UserID FROM relations WHERE GroupID = '1')
OR
Select * from user_table u INNER JOIN relations r ON u.UserID=r.UserID WHERE r.GroupID='1'
回答by Madhivanan
select * from user_typw
where userID in (SELECT UserID FROM relations WHERE GroupID = '1')
回答by Joe G Joseph
try this
尝试这个
select * from user where UserID in
(SELECT UserID FROM relations WHERE GroupID = '1')
or
或者
select * from user U where exists
(SELECT * FROM relations R WHERE U.UserID=R.UserID and R.GroupID = '1')
or
或者
select U.*
from user U join relations R
on U.UserID=R.UserID
where R.GroupID = '1'
回答by Manatok
SELECT * FROM user_table ut LEFT JOIN relations r on ut.UserID=r.UserID where r.GroupID=1;
回答by Mohit Singh
SELECT A.field1,B.field2 FROM table1 A LEFT JOIN table2 B ON A.common_ID=B.common_ID WHERE field = 'abc'
SELECT A.field1,B.field2 FROM table1 A LEFT JOIN table2 B ON A.common_ID=B.common_ID WHERE field = 'abc'
