SQL联合导致“将varchar值转换为int时转换失败”

I tried to search for previous articles related to this, but I can't find one specific to my situation. And because I'm brand new to stack overflow, I can't post pictures so I'll try to describe it.

我试图搜索以前与此相关的文章，但我找不到一个特定于我的情况。因为我是堆栈溢出的新手，我不能发布图片所以我会尝试描述它。

I have two datasets. One is 34 rows, 1 column of all NULLs. The other 13 rows, 1 column of varchars.

我有两个数据集。一个是34行，1列是所有NULL。其他13行，1列varchars。

When I try to union all these two together, i get the "Conversion failed when converting the varchar value to data type int." I don't understand why I'm getting this error. I've unioned many null columns and varchar columns before, among many other types and I don't get this conversion error. Can anyone offer suggestions why this error occurs?

当我尝试将所有这两者结合在一起时，我得到“将varchar值转换为数据类型int时转换失败”。我不明白为什么我收到这个错误。我之前已经联合了许多空列和varchar列，在许多其他类型中，我没有得到这个转换错误。任何人都可以提出为什么会出现此错误？

1 个解决方案

#1

The error occurs because you have corresponding columns in the two of the subqueries where the type of one is an integer and the type of the other is a character. Then, the character value has -- in at least one row -- a value that cannot be automatically converted to an integer.

发生此错误的原因是，在两个子查询中有相应的列，其中1的类型是整数，而另一个的类型是字符。然后，字符值至少在一行中具有一个不能自动转换为整数的值。

This is easy to replicate:

这很容易复制：

select t.*
from (select 'A' as col union all
      select 1
     ) t;

Here is the corresponding SQL Fiddle.

这是相应的SQL小提琴。

SQL Server uses pretty sophisticated type precedence rules for determining the destination type in a union. In practice, though, it is best to avoid using implicit type conversions. Instead, explicitly cast the columns to the type you intend.

SQL Server使用相当复杂的类型优先级规则来确定联合中的目标类型。但实际上，最好避免使用隐式类型转换。相反，显式地将列转换为您想要的类型。

EDIT:

编辑：

The situation with NULL values is complicated. By itself, the NULL value has no type. So, the following works fine:

NULL值的情况很复杂。 NULL值本身没有类型。所以，以下工作正常：

select NULL as col
union all
select 'A';

If you type the NULL, then the query will fail:

如果键入NULL，则查询将失败：

select cast(NULL as int) as col
union all
select 'A';

Also, if you put SQL Server in a position where it has to assign a type, then SQL Server will make the NULL an integer. Every column in a table or result set needs a type, so this will also fail:

此外，如果将SQL Server置于必须分配类型的位置，则SQL Server将使NULL成为整数。表或结果集中的每一列都需要一个类型，因此也会失败：

select (select NULL) as col
union all
select 'A';

Perhaps your queries are doing something like this.

也许您的查询正在做这样的事情。

#1