SQL UNION ALL 两个具有不同列类型的 SELECTs - 预期行为？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30049489/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNION ALL two SELECTs with different column types - expected behaviour?
提问by alex
What is the expected behaviour due to SQL Standard when we perform UNIONon two tables with different data types:
当我们UNION对具有不同数据类型的两个表执行时，由于 SQL Standard 的预期行为是什么：
create table "tab1" ("c1" varchar(max));
create table "tab2" ("c3" integer);
insert into tab1 values(N'asd'), (N'qweqwe');
insert into tab2 values(123), (345);
select
c_newname as myname
from
(
select "c1" as c_newname from "tab1"
union all
select "c3" from "tab2"
) as T_UNI;
MS SQL Servergives
MS SQL Server给
Conversion failed when converting the varchar value 'asd' to data type int.
将 varchar 值“asd”转换为数据类型 int 时转换失败。
but what is defined in the standard?
但是标准中定义了什么？
采纳答案by Robert
If you want to use union allcolumns in every query need to have the same type.C3must be converteted to varchar because c1is varchar. Try below solution
如果要union all在每个查询中使用列需要具有相同的类型。C3必须转换为 varchar，因为c1是 varchar。尝试以下解决方案
create table "tab1" ("c1" varchar(max));
create table "tab2" ("c3" integer);
insert into tab1 values(N'asd'), (N'qweqwe');
insert into tab2 values(123), (345);
select
c_newname as myname
from
(
select "c1" as c_newname from "tab1"
union all
select cast("c3" as varchar(max)) from "tab2"
) as T_UNI;
I replaced "tab3"with "tab1"- I think it's typo.
我替换"tab3"为"tab1"- 我认为这是错字。
回答by Salman A
From T-SQL UNIONpage:
从T-SQL UNION页面：
The following are basic rules for combining the result sets of two queries by using UNION:
- The number and the order of the columns must be the same in all queries.
- The data types must be compatible.
以下是使用 UNION 组合两个查询的结果集的基本规则：
- 在所有查询中，列的数量和顺序必须相同。
- 数据类型必须兼容。
When one datatype is VARCHARand other is INTEGERthen SQL Server will implicitly attempt to convert VARCHARto INTEGER(the rules are described in the precedence table). If conversion fails for any row, the query fails. So this works:
当一种数据类型是VARCHAR，另一种是INTEGERSQL Server 将隐式尝试转换VARCHAR为INTEGER（规则在优先级表中描述）。如果任何行的转换失败，则查询失败。所以这有效：
INSERT INTO #tab1 VALUES(N'123'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2
But this does not:
但这不会：
INSERT INTO #tab1 VALUES(N'ABC'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2
-- Conversion failed when converting the varchar value 'ABC' to data type int.
The rules for conversion are described here:
此处描述了转换规则：
Having said that, you can explicitlyconvert your integer data to varchar in order to make the query work (the datatype of result would be varchar).
话虽如此，您可以显式地将整数数据转换为 varchar 以使查询工作（结果的数据类型将是 varchar）。
回答by Hell Boy
The basic rule is--> Either the datatype used should be same in two table(or)you should use cast or convert function to match the datatypes in those two tables.
基本规则是--> 两个表中使用的数据类型应该相同（或者）您应该使用强制转换或转换函数来匹配这两个表中的数据类型。
SQL Standard:
SQL标准：
1)The number and the order of the columns must be the same in all queries.
2)Column datatypes must be compatible: They need not be the same exact same type, but they must be of a type that SQL Server can implicitly convert.
1）所有查询中列的数量和顺序必须相同。
2）列数据类型必须兼容：它们不需要完全相同的类型，但它们必须是 SQL Server 可以隐式转换的类型。
