T-SQL 查询在声明整数与在查询中计算时给出不同的结果 [英] T-SQL Query Gives Different Results when declaring integer vs calculating in-query

问题描述

所以我一直在尝试在查询中生成一些 SQL 随机数，我注意到一些奇怪的事情.

So I've been experimenting with some SQL random number generation in a query, and I noticed something odd.

假设我运行以下查询:

declare @Random int = CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1;

select CHOOSE(@Random,'One','Two','Three','Four','Five')

SQL random number gen 有点笨重，但这里的基本思想很简单 - 在 1 到 5 之间选择一个随机数，然后将该数字作为文本显示到选择窗口.这按预期工作.

SQL random number gen is a little bulky, but the basic idea here is simple - pick a random number between 1 and 5, then display that number as text to the select window. This works as expected.

但是，如果我采用 SAME 查询，但将随机数公式粘贴到方法中而不是将其声明为整数，则所有内容都在一行中:

However, if I take the SAME query, but paste the random number formula into the method instead of declaring it as an integer, so it's all on one line:

select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
    1,'One','Two','Three','Four','Five')

当我运行查询时，我仍然得到值一到五，但有时我也会得到一个 NULL.NULL 经常出现，大约五分之一.如果我将两个查询都放入 ssms 并并排运行几次，我经常会看到第二个查询的 null 值，但第一个查询永远不会为 NULL.

I still get values One to Five when I run the query, but sometimes I also get a NULL. The NULL comes up pretty often, about one in 5 times. If I put both queries into ssms and run them next to each other a few times, I often see a null value for the second query, but the first is never NULL.

那是为什么?这不是完全一样的计算吗?我不知道为什么这两个查询会给出不同的结果，但我觉得通过查找我可以学到一些关于 T-SQL 的有用信息.

So why is that? Aren't these exactly the same calculation? I have no idea why these two queries would give different results, but I feel like I can learn something useful about T-SQL by finding out.

有哪位高手给我指点一下吗?

Any experts want to enlighten me?

推荐答案

这是 SQL Server 中 choose 函数的一个非常微妙的问题(好吧，Microsoft 可能认为它是一个功能).该函数实际上是 case 表达式的简写.所以，你的表情:

This is a very subtle problem with the choose function in SQL Server (well, Microsoft probably considers it a feature). The function is really shorthand for a case expression. So, your expression:

select CHOOSE(CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) +
1,'One','Two','Three','Four','Five')

翻译成:

select (case when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 1 then 'One'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 2 then 'Two'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 3 then 'Three'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 4 then 'Four'
             when CAST(RAND(CHECKSUM(NEWID())) * 5 as INT) + 1 = 5 then 'Five'
        end)

这意味着 newid() 被多次调用.这通常不是您想要的行为.

That means that the newid() is being called multiple times. This is usually not the behavior that you want.

这篇关于T-SQL 查询在声明整数与在查询中计算时给出不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！