T-SQL Case 语句以 newid() 作为随机源的奇怪行为

我正在使用 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

我在这里做错了什么？

Your:

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

COALESCE 表达式是 CASE 的语法快捷方式 表达。即代码COALESCE(表达式1,...n)被重写 由查询优化器将其定义为以下 CASE 表达式：

CASE

WHEN (表达式 1 IS NOT NULL) THEN 表达式 1

WHEN (表达式2 IS NOT NULL) THEN 表达式2

...

ELSE 表达式

END

这意味着输入值 (表达式1, 表达式2, 表达式N等）将被评估多次。另外，在 符合SQL标准，包含一个值表达式 子查询被认为是非确定性的，并且子查询被评估 两次。无论哪种情况，都可能返回不同的结果 首次评估和后续评估.

EDIT:

解决方案：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)