SQL 消息 512，级别 16，状态 1，第 2 行子查询返回了 1 个以上的值。当子查询跟随时这是不允许的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24755137/
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
Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows
提问by Datta
how to solve this kind of probe help me.
如何解决这种探针帮助我。
Select SUM(iamount)
from cust_installment
where c_no=(
Select b.c_no
from a_basic a
INNER JOIN cust_personal b ON a.a_code=b.a_code
INNER JOIN cust_installment c ON b.c_no=c.c_no
where c.idate BETWEEN '2014-06-25' AND '2014-06-25')
error is
错误是
Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
消息 512，级别 16，状态 1，第 2 行子查询返回了 1 个以上的值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。
回答by Jon Egerton
In your main where clause you have where c_no=( <<SUBQUERY>> ). The inner subquery here must return a single result, otherwise that where-clause doesn't make sense.
在您的主要 where 子句中，您有where c_no=( <<SUBQUERY>> ). 这里的内部子查询必须返回单个结果，否则 where-clause 没有意义。
So either restrict the subquery to a single result by using top 1or by fixing it if logically it should only ever return a single result.
因此top 1，如果逻辑上它应该只返回一个结果，要么通过使用或修复它来将子查询限制为单个结果。
Alternatively, if you're expecting to match multiple results, change the main where clause to where c_no in ( <<SUBQUERY>> )
或者，如果您希望匹配多个结果，请将主要 where 子句更改为 where c_no in ( <<SUBQUERY>> )
回答by Tomtom
Your subquery
您的子查询
Select b.c_no from a_basic a INNER JOIN cust_personal b ON a.a_code=b.a_code INNER JOIN cust_installment c ON b.c_no=c.c_no where c.idate BETWEEN '2014-06-25' AND '2014-06-25'
can return more than one value.
可以返回多个值。
You have several opportunities:
你有几个机会：
- Use Select TOP 1 b.c_no from a_basic a INNER JOIN...
- Replace the
=within
- 使用 Select TOP 1 b.c_no from a_basic a INNER JOIN...
- 更换
=用in
回答by Azar
Try this
尝试这个
Select SUM(iamount)
from cust_installment
where c_no in (
Select b.c_no
from a_basic a
INNER JOIN cust_personal b ON a.a_code=b.a_code
INNER JOIN cust_installment c ON b.c_no=c.c_no
where c.idate BETWEEN '2014-06-25' AND '2014-06-25')
