选择返回null

Question

SELECT
    r.[value] as KnownRandomNumber,
    CHOOSE(r.[value], 5, 15, 16, 17) AS KnownRandomResult,
    CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17) AS UnknownRandomResult
FROM
    (SELECT FLOOR(RAND() * 4) + 1 AS [value]) r

为什么CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17)有时会返回null？即使使用从子查询中获得的随机数也不会返回null？

我在SQL Server Management Studio中的Microsoft SQL Server上运行它。

​

​

Answer 1

我想你不在索引里了。如果不是这样，您就会发现每次执行相同的查询时，RAND()都返回相同的值。问题出在这里的逻辑，而不是函数'CHOOSE‘

测试并使用它，查看每个字段的值以及它的工作原理：

SELECT
    CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17,'OUT') AS UnknownRandomResult,
    RAND() * 4 as Rand4,
    FLOOR(RAND() * 4)  as Floor4,
    FLOOR(RAND() * 4) + 1 as Floor4Plus1,
        RAND() * 4 as Rand4_2,
    FLOOR(RAND() * 4)  as Floor4_2,
    FLOOR(RAND() * 4) + 1 as Floor4Plus1_2

更多信息请阅读：https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql?view=sql-server-ver15

更新：

我建议使用一些如果为空默认值来修复该空值。