是否可以在另一个SELECT语句中使用SELECT语句的结果? - Is it possible to use results from a SELECT statement within another SELECT statement?

Is it possible to use results from a SELECT statement within another SELECT statement?

这是我的代码，但我确定这不是正确的方法。

mysql_query("SELECT * FROM BlockUsers
WHERE OwnerID =
(SELECT ID FROM UserAccounts WHERE Code='$UserCode')
AND
BlockID =
(SELECT ID FROM UserAccounts WHERE Code='$BlockUserCode')
LIMIT 1", $DB)

有人能帮忙吗?谢谢!

是的，但是当您做这样的相等性测试(=，<，>等…)时，子查询必须返回单个值。否则就是somevalue = [list of result rows]，这就没有意义了。

你想要

SELECT * FROM BlockUsers
WHERE OwnerID IN (SELECT ID FROM UserAccounts WHERE.....)
              ^^--- use 'in' instead of '=';

SELECT * FROM BlockUsers
WHERE OwnerID IN
(SELECT ID FROM UserAccounts WHERE Code='$UserCode')
AND
BlockID IN
(SELECT ID FROM UserAccounts WHERE Code='$BlockUserCode')
LIMIT 1

或

SELECT * FROM BlockUsers AS bu
INNER JOIN UserAccounts AS ua1 ON ua1.ID = bu.OwnerID
INNER JOIN UserAccounts AS ua2 ON ua2.ID = bu.BlockID
WHERE ua1.Code = '$UserCode' AND ua2.Code = '$BlockUserCode'
LIMIT 1

我认为

。我没有测试这些，但我很确定它很接近。

编辑:我刚刚注意到你正在使用MySQL。你肯定希望使用内连接而不是子选择。在MySQL中，这些子选择将创建没有索引的派生表。在这些派生表中查找OwnerID和BlockID将对它们进行全表扫描。如果$UserCode和$BlockUserCode将子选择的结果缩小到单行，这可能无关紧要，但如果它们返回相当多的行，它将真正减慢您的查询速度。

不使用子查询，您可以直接JOIN UserAccounts来获得您想要的行。

SELECT BlockUsers.* FROM BlockUsers
JOIN UserAccounts as UA1 ON Code='$UserCode' AND OwnerID = UA1.ID
JOIN UserAccounts as UA2 ON Code='$BlockUserCode' AND BlockID = UA2.ID
LIMIT 1

是的，你可以，子查询，你可以在这里找到官方mysql参考:http://dev.mysql.com/doc/refman/5.0/en/subqueries.html从你的查询，我认为你很好去假设你的子查询只返回1个值，或者@Marc B点使用IN而不是等号。

尝试使用

SELECT * FROM BlockUsers, UserAccounts
WHERE (OwnerID = ID and BlockID=ID and Code in ('$BlockUserCode', '$UserCode')
LIMIT 1