SQL Server 2008中的case子查询 [英] case subquery in sql server 2008

问题描述

以下语句给出错误

打印(大小写) 存在时(从tblCustomerProductsDiscount PD中选择count(*)，其中PD.cust_ID = 138和PD.pack_detl_ID = 1)然后为0结束)

print (case when exists (select count(*) from tblCustomerProductsDiscount PD where PD.cust_ID=138 and PD.pack_detl_ID = 1) then 0 end)

错误: 在这种情况下，不允许子查询.仅允许标量表达式.

Error: Subqueries are not allowed in this context. Only scalar expressions are allowed.

推荐答案

首先，尽管您的意图很明确，但当前形式的脚本没有任何意义，这就是原因.

First of all, while your intention is quite clear, the script in its current form doesn't make sense, and here's why.

您正在检查select count(*)...子选择中是否存在行，但事实是COUNT() 始终返回一个值.如果在指定条件下没有行，它将返回0，但仍将是子查询返回的行，并且在任何情况下EXISTS的结果都将为TRUE.

You are checking for the existence of rows in the select count(*)... subselect, but the fact is, COUNT() always returns a value. In case of no rows for the specified condition it will return 0, but that would still be a row returned by the subquery, and EXISTS would evaluate to TRUE in any case.

要修复此问题，只需将select count(*)替换为select *.

To fix it, just replace select count(*) with select *.

另一件事是错误. 在这种情况下不允许子查询，即最终.使用PRINT，您不能以任何形式使用子查询.将结果存储在变量中，并PRINT变量:

Another thing is the error. Subqueries are not allowed in this context, and that is final. With PRINT you cannot use a subquery in any form. Store the result in a variable and PRINT the variable:

declare @result int;
set @result = case
  when exists (
    select *
    from tblCustomerProductsDiscount PD
    where PD.cust_ID=138 and PD.pack_detl_ID = 1
  )
    then 0
end

print @result;

这篇关于SQL Server 2008中的case子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！