SQL 如何处理子查询返回多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9156879/
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
How to handling if subquery returns multiple value
提问by LovingMVC
I have a stored procedure in which I'm using sub query, but the issue is that my sub query returns 2 values and I've to return all records based on these two values.
我有一个使用子查询的存储过程，但问题是我的子查询返回 2 个值，我必须根据这两个值返回所有记录。
Select * from [Address] where AddressID=
(Select AddressID from PersonAddress where PersonID=
(select Claimant from [Case] where CaseID=35))
In this query AddressID returning two values and both the value having record in table, I've to return both the address.
在这个查询 AddressID 返回两个值和表中都有记录的值，我必须返回两个地址。
How can I solve this?
我该如何解决这个问题？
回答by aF.
Instead of =use IN:
而不是=使用IN：
Select * from [Address] where AddressID IN
(Select AddressID from PersonAddress where PersonID IN
(select Claimant from [Case] where CaseID=35))
or try JOIN, the correct way:
或尝试JOIN，正确的方法：
Select * from Address a
inner join PersonAddress p on a.AdressID = p.AddressID
inner join Case c on p.PersonID = c.Claimant
where c.CaseID = 35
回答by Oleg Dok
You have two options:
您有两个选择：
use IN clause like this:
Select * from [Address] where AddressID IN ( Select AddressID from PersonAddress where PersonID IN (select Claimant from [Case] where CaseID=35) )
or limit your subqueries with TOP clause
Select * from [Address] where AddressID= (Select TOP 1 AddressID from PersonAddress where PersonID= (select TOP 1 Claimant from [Case] where CaseID=35))
像这样使用 IN 子句：
Select * from [Address] where AddressID IN ( Select AddressID from PersonAddress where PersonID IN (select Claimant from [Case] where CaseID=35) )
或使用 TOP 子句限制您的子查询
Select * from [Address] where AddressID= (Select TOP 1 AddressID from PersonAddress where PersonID= (select TOP 1 Claimant from [Case] where CaseID=35))
回答by Arion
Something like this:
像这样的东西：
SELECT
*
FROM
[Address]
WHERE EXISTS
(
SELECT
NULL
from
PersonAddress
WHERE
PersonAddress.AddressID=[Address].AddressID
AND EXISTS
(
SELECT
NULL
FROM
[Case]
WHERE
[Case].CaseID=35
AND PersonAddress.PersonID=[Case].Claimant
)
)
