sql-server – 在SQL中选择CASE与CASE

某种程度上不正确但工作语法,返回错误结果,例如,当比较两个相等的值时,它返回0：

(SELECT CASE 
    WHEN 
       SUM(V.IsCompatible) OVER 
          (PARTITION BY ComputerName,UserID) = ApplicationCount 
    THEN 1 ELSE 0 END
) AS CompatibleUser

下面的那个返回正确的值,即. 1当有两个相等的值进行比较时.

(CASE 
    WHEN 
       SUM(V.IsCompatible) OVER 
         (PARTITION BY ComputerName,UserID) = ApplicationCount 
    THEN 1 ELSE 0 END
) AS CompatibleUser

甚至更简单：

(SELECT CASE 
    WHEN 
       X = Y 
    THEN 1 ELSE 0 END
) AS Result

X = 22且Y = 22 =>结果= 0

(CASE 
    WHEN 
       X = Y 
    THEN 1 ELSE 0 END
) AS Result

X = 22且Y = 22 =>结果= 1

我理解应用正确的语法很重要,我知道T-SQL中的SELECT CASE语法,但我不明白如何评估第一个代码示例并提供意外结果.

更新：在其上下文中的完整查询

select userapplication.username,computerdetails.computername,sum(userapplication.iscompatible) 
       over (partition by computerdetails.computername,userapplication.userid) as compatiblecount,userapplication.applicationcount,( case
       when sum(userapplication.iscompatible) 
           over (partition by 
           computerdetails.computername,userapplication.userid) <> userapplication.applicationcount 
       then 0
       else 1
     end 
   ) as usercomputeriscompatible
from   computerdetails
   right outer join usercomputer
     on computerdetails.computerid = usercomputer.computerid
   right outer join userapplication
     on usercomputer.gebruikerid = userapplication.userid

所以userComputerIsCompatible是这里的问题

解决方法

参数：分析(SELECT SUM(IsWeb)OVER(PARTITION BY OrderDate)[FROM current_row])表达式的执行计划

在Segment和Stream Aggregate([Expr1007] =标量运算符(SUM(@OrderHeader.[IsWeb]为[h].[IsWeb])))运算符之前,我看到一个Constant Scan (Scan an internal table of constants)运算符而不是Clustered Index Scan.此内部表(恒定扫描)由当前行构成.

示例(使用SQL2005SP3和SQL2008测试)：

DECLARE @OrderHeader TABLE
(
     OrderHeaderID  INT IDENTITY PRIMARY KEY,OrderDate      DATETIME NOT NULL,IsWeb          TINYINT NOT NULL --or BIT
);
INSERT  @OrderHeader 
SELECT  '20110101',0
UNION ALL 
SELECT  '20110101',1
UNION ALL
SELECT  '20110101',1
UNION ALL 
SELECT  '20110102',1
UNION ALL
SELECT  '20110103',0
UNION ALL
SELECT  '20110103',0;

SELECT  *,SUM(IsWeb) OVER(PARTITION BY OrderDate) SumExpression_1
FROM    @OrderHeader h
ORDER BY h.OrderDate;

SELECT  *,(SELECT SUM(IsWeb) OVER(PARTITION BY OrderDate)) SumWithSubquery_2
FROM    @OrderHeader h
ORDER BY h.OrderDate;

结果：

OrderHeaderID OrderDate               IsWeb SumExpression_1
------------- ----------------------- ----- ---------------
1             2011-01-01 00:00:00.000 0     2
2             2011-01-01 00:00:00.000 1     2
3             2011-01-01 00:00:00.000 1     2
4             2011-01-02 00:00:00.000 1     1
5             2011-01-03 00:00:00.000 0     0
6             2011-01-03 00:00:00.000 0     0

OrderHeaderID OrderDate               IsWeb SumWithSubquery_2
------------- ----------------------- ----- -----------------
1             2011-01-01 00:00:00.000 0     0
2             2011-01-01 00:00:00.000 1     1
3             2011-01-01 00:00:00.000 1     1
4             2011-01-02 00:00:00.000 1     1
5             2011-01-03 00:00:00.000 0     0
6             2011-01-03 00:00:00.000 0     0

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!