SQL Server 错误:“SQL Server 子查询返回的值超过 1" [英] SQL Server Error:"SQL Server Subquery returned more than 1 value"

问题描述

子查询返回的值超过 1.当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的."

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

我在调试以下代码时收到此有线错误消息:

I got this wired error message while debugging the following code:

    WHEN NOT EXISTS(SELECT  1
                    FROM    defs WITH(NOLOCK)
                    WHERE   defaultname = @DEFAULT_CURRENCY
                    AND defaultvalue= @currency)
    AND NOT EXISTS( SELECT  1
                    FROM    supp WITH(NOLOCK)
                    WHERE   pint    = @ID
                    AND currency= @currency)
           THEN "NOT VALID." 

关于错误消息，我的代码没有发现任何问题.此外，当我将 'AND' 更改为 'OR' 时，此错误消息消失了.

I couldn't find anything wrong with my code regarding to the error message. Moreover, this error message disappear when I changed 'AND' to 'OR'.

    WHEN NOT EXISTS(SELECT  1
                    FROM    defs WITH(NOLOCK)
                    WHERE   defaultname = @DEFAULT_CURRENCY
                    AND defaultvalue= @currency)
    OR NOT EXISTS(  SELECT  1
                    FROM    supp WITH(NOLOCK)
                    WHERE   pint    = @ID
                    AND currency= @currency)
            THEN "NOT VALID." 

我需要使用AND"条件，但如何消除错误?

I need to use 'AND' condition, but how to remove the error?

(代码片段中的所有@ 变量都不是表变量.)

(All @ variable in the code snippet is not table variable.)

提前致谢.

推荐答案

该错误告诉您是您的子查询之一，但我不认为这些子查询是导致错误的原因.如果您使用 and 'exist'(或 'notexist' )，您也可以使用 TOP 1，因为您只需要检查一行.

The error tells you that one of your subqueries, but i don't think these subqueries are the one that give the error. If you use and 'exist' ( or 'not exist' ) you can also use TOP 1, because you only need to check one row.

这意味着您可以使用:

WHEN NOT EXISTS(SELECT TOP 1 1
                    FROM    defs WITH(NOLOCK)
                    WHERE   defaultname = @DEFAULT_CURRENCY
                    AND defaultvalue= @currency)
    OR NOT EXISTS(  SELECT TOP 1  1
                    FROM    supp WITH(NOLOCK)
                    WHERE   pint    = @ID
                    AND currency= @currency)

但这不是导致您出错的原因.此示例代码执行相同的操作并且工作正常:

But this is not what is causing your error. This example code does that same and works fine:

declare @table1 table (
    id int,
    value nvarchar(100)
)

declare @subquery table (
    id int,
    value nvarchar(100)
)

insert into @table1 values (1, 'one')
insert into @table1 values (2, 'two')
insert into @table1 values (3, 'three')
insert into @subquery values (1, 'also one')
insert into @subquery values (3, 'also three')
insert into @subquery values (5, 'also five')


select * from @table1 sourceTable
where not exists (select 1 from @subquery where id = sourceTable.id)
     and not exists (select 1 from @subquery where value = sourceTable.value)

所以我认为它必须在您查询的另一部分.

So I think it must be in another part of your query.

这篇关于SQL Server 错误:“SQL Server 子查询返回的值超过 1"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！