sql-server - 使用 newid() 作为随机源的 T-SQL Case 语句奇怪的行为

Question

我正在使用 SQL Server 2012。

如果我执行以下操作以获取范围 [1,3] 中的随机数列表，则效果很好:

SELECT TOP 100 
    ABS(CHECKSUM(NEWID()))%3 + 1 [value_of_rand]
FROM sys.objects

我得到了这样的好东西(都在 1 到 3 之间)。

3
2
2
2
1
....etc.

但是，如果我随后将相同的链式随机值函数的输出放入 CASE 语句中，它显然不会只生成值 1、2、3。

SELECT TOP 100 
    CASE (ABS(CHECKSUM(NEWID()))%3 + 1)
        WHEN 1
            THEN 'one'
        WHEN 2
            THEN 'two'
        WHEN 3
            THEN 'three'
        ELSE
            'that is strange'
    END [value_of_case]
FROM sys.objects

它输出:

three
that is strange
that is strange
one
two
...etc

我在这里做错了什么？

Answer 1

您的:

SELECT TOP 100 
    CASE (ABS(CHECKSUM(NEWID()))%3 + 1)
        WHEN 1
            THEN 'one'
        WHEN 2
            THEN 'two'
        WHEN 3
            THEN 'three'
        ELSE
            'that is strange'
    END [value_of_case]
FROM sys.objects

实际执行:

SELECT TOP 100 
    CASE 
        WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 1  THEN 'one'
        WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 2  THEN 'two'
        WHEN (ABS(CHECKSUM(NEWID()))%3 + 1) = 3  THEN 'three'
        ELSE 'that is strange'
    END [value_of_case]
FROM sys.objects;

基本上你的表达式是不确定的，每次都被评估，所以你可以得到 ELSE clause .所以没有错误或捕获，只需将它与变量表达式一起使用，这是完全正常的行为。

这是与 COALESCE syntactic-sugar 相同的类

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

This means that the input values (expression1, expression2, expressionN, etc.) will be evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In either case, different results can be returned between the first evaluation and subsequent evaluations.

编辑:

解决方案:
SqlFiddle

SELECT TOP 100 
    CASE t.col
        WHEN 1     THEN 'one'
        WHEN 2     THEN 'two'
        WHEN 3     THEN 'three'
        ELSE      'that is strange'
    END [value_of_case]
FROM sys.objects
CROSS APPLY ( SELECT ABS(CHECKSUM(NEWID()))%3 + 1 ) AS t(col)