操作数数据类型 void 类型对于 sum 运算符无效 [英] Operand data type void type is invalid for sum operator

问题描述

我有一个关于 null 使用的问题.

I would have a question about the use of null.

如果我运行这个查询

SELECT SUM(x)
FROM (SELECT NULL AS x) AS DerivedTable

我收到错误信息

求和运算符的操作数数据类型 void 类型无效."

"Operand data type void type is invalid for sum operator."

但是，如果我创建一个带有可为空字段的表并在 sum 聚合中使用它，它将忽略 NULL 值并基本上将所有其余记录相加.

However, if I create a table with a nullable field and use it within the sum aggregate, it will ignore the NULL value and basically add up all the rest of the records.

CREATE TABLE #temp1(x int)
insert into #temp values (NULL),(3),(5)

SELECT SUM(x) FROM #temp1 

结果是 8.

对这个有一个合乎逻辑的解释吗?会不会是因为 NULL 的数据类型没有在派生表的查询中声明，而是在临时表中声明的?另外，我真的很想知道是否有办法在从表或派生表运行时产生此错误消息，就像第二个 #temp1 示例一样.任何在从表或表表达式运行时产生此错误消息的示例都非常受欢迎.

Is there a logical explanation of this one? Would it be because of the data type of NULL has not been declared on the query with the derived table but was declared with the temp table? Also, I would really like to know if there is a way to produce this error message when run from a table or a derived table, just like the second #temp1 example. Any example which produces this error message when run from a table or a table expression is highly welcome.

谢谢大家

推荐答案

这确实是因为在您的派生表方案中无法推断列的类型.

This is indeed because the type of the column cannot be inferred in your derived table scenario.

如果您更改语句并在列上显式使用类型，则选择将起作用.

If you change the statement and explicitly use a type on the column, the select will work.

SELECT SUM(x) FROM (SELECT CAST(NULL AS INTEGER) AS x) AS DerivedTable

以下语句可以推断类型

SELECT SUM(x) FROM (SELECT NULL AS x UNION SELECT 1) AS DerivedTable

SQL 小提琴

这也可以回答您的第二个问题.由于您无法在不声明类型的情况下创建表，因此无法生成会生成该特定错误消息的语句.

This would also answer your second question. As you can not create a table without declaring the types, there's no way to produce a statement that would generate that specific error message.

这篇关于操作数数据类型 void 类型对于 sum 运算符无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！