MySQL 子查询返回多行..如何处理？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15440620/
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
subquery returning multiple rows..how to deal with it?
提问by user2175325
SELECT requestID
FROM request
WHERE userId = (
SELECT userID
FROM department
WHERE desig = 'E'
AND dept = (
SELECT dept
FROM department
WHERE userId = it18
AND desig = 'FM'
)
);
回答by Michael Fredrickson
It would be much clearer to write this query using JOIN:
使用JOIN以下命令编写此查询会更清晰：
select distinct r.requestID
from
request r
join department d
on d.userId = r.userID
and desig = 'E'
join department d2
on d2.dept = d.dept
and d2.desig = 'FM'
and d2.userId = 'it18'
Alternately, You could simply replace the =with IN:
或者，你可以简单地替换=有IN：
SELECT requestID
FROM request
WHERE userId IN (
SELECT userID
FROM department
WHERE desig = 'E'
AND dept IN (
SELECT dept
FROM department
WHERE userId = it18
AND desig = 'FM'
)
);
They should return identical results, but try both to see if there's any difference in performance.
它们应该返回相同的结果，但尝试两者以查看性能是否有任何差异。
回答by Bohemian
There are two ways of dealing with it:
有两种处理方法：
Option 1: Change to WHERE ... IN (SELECT ...), like this:
选项 1：更改为WHERE ... IN (SELECT ...)，如下所示：
SELECT requestID
FROM request
WHERE userId IN (
SELECT userID
FROM department
WHERE desig = 'E'
AND dept IN (
SELECT dept
FROM department
WHERE userId = it18
AND desig = 'FM'
)
);
Option 2: Force only one result by using an aggregate function like MAX(), like this:
选项 2：通过使用MAX()像这样的聚合函数只强制一个结果，如下所示：
SELECT requestID
FROM request
WHERE userId = (
SELECT MAX(userID)
FROM department
WHERE desig = 'E'
AND dept = (
SELECT MAX(dept)
FROM department
WHERE userId = it18
AND desig = 'FM'
)
);
