我不太明白为什么这两个不同的代码示例会返回不同的值.
某种程度上不正确但工作语法,返回错误结果,例如,当比较两个相等的值时,它返回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 …)这样的表达式被认为是子查询,即使它们没有FROM子句.假设这些(错误)“子查询”的数据源仅是当前行.因此,(SELECT表达式)被解释为(SELECT表达式FROM current_row)和(SELECT SUM(iscompatible)OVER(…))被执行为(SELECT SUM(iscompatible)OVER(current_row)).
参数：分析(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.此内部表(恒定扫描)由当前行构成.
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
