CASE表达式返回异常NULL值：SQL加权随机编码问题求助

异常原因 #

你在第二个查询的CASE表达式中，直接将调用crypt_gen_random(1)的逻辑嵌入到了CASE的判断条件里。由于crypt_gen_random是非确定性函数（每次调用都会生成新的随机值），SQL Server的查询优化器可能会在执行过程中多次调用该函数，导致CASE判断时使用的随机值和实际截取字符的随机值不一致。比如，某次执行时，截取字符得到的是'1'，但CASE比较时又生成了新的随机值，导致没有匹配到任何WHEN分支，最终返回NULL。

简单来说：同一个行里，CASE表达式里的随机函数被执行了多次，导致截取的字符和判断的字符不是同一个，从而出现无匹配的情况，返回NULL。

解决办法 #

先将随机生成的字符提取到一个临时子查询或CTE的列中，确保每行只调用一次随机函数，再对这个固定的字符进行CASE映射。这样就能保证CASE判断使用的是同一个随机生成的字符，不会出现无匹配的NULL。

修改后的代码如下：

--
--  Expecting 1 = 50% , 2 = 35%, 3 = 15%
--
declare @weightedKeys varchar(100)
select @weightedKeys =
    '11111111111111111111111111111111111111111111111111' + -- 50
    '22222222222222222222222222222222222' + -- 35
    '333333333333333' -- 15
;with a(k) as
(
    select 1 as k
    union all
    select k + 1 from a where k < 99+1
),
t2 as
(
    select row_number() over(order by x.k) as k
    from a x, a y, a z 
), 
chance as
(
    -- 先在子查询中生成固定的随机字符，再进行CASE映射
    select
        case temp.key_char
            when '1' then '1-One' 
            when '2' then '2-Two'
            when '3' then '3-Three'
        end as test_group
    from (
        select
            substring(@weightedKeys, ((convert(tinyint,crypt_gen_random(1))*100)/256)+1, 1) as key_char
        from t2
    ) as temp
)
--check the results
select test_group, count(1) as total
from chance
group by test_group
order by count(1) desc

验证效果 #

修改后，每个行只会调用一次crypt_gen_random生成随机位置，截取到的字符必然是'1'/'2'/'3'中的一个，CASE映射时能准确匹配到对应的分支，不会再出现NULL值，同时保持1:50%、2:35%、3:15%的权重比例。

内容的提问来源于stack exchange，提问作者Ian Wells