SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
下面的例子中使用的原始表：
Employees_China:
| E_ID | E_Name |
|---|---|
| 01 | Zhang, Hua |
| 02 | Wang, Wei |
| 03 | Carter, Thomas |
| 04 | Yang, Ming |
Employees_USA:
| E_ID | E_Name |
|---|---|
| 01 | Adams, John |
| 02 | Bush, George |
| 03 | Carter, Thomas |
| 04 | Gates, Bill |
使用 UNION 命令
实例
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
结果
| E_Name |
|---|
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Gates, Bill |
注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。
UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。
SQL Statement 1 UNION ALL SQL Statement 2
使用 UNION ALL 命令
实例：
列出在中国和美国的所有的雇员：
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
结果
| E_Name |
|---|
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
让UNION与ORDER BY并存于SQL语句当中
在SQL语句中，UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表)，简单实例如下：
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product2
]
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product2
]
上面的代码可以实现将从Product1和Product2两张表合并成一个表，如果您只是希望合并两张表中符合特定条件的记录抑或是合并两张表各自的前N条记录，那么您的代码可能会像下面这样写：
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
WHERE
LEN([
Name
]
) > 5
UNION
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product2
]
WHERE
[
Id
]
IN
(
11
,
20
)
AND
[
Comment
]
IS
NOT
NULL
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
WHERE
LEN([
Name
]
) > 5
UNION
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product2
]
WHERE
[
Id
]
IN
(
11
,
20
)
AND
[
Comment
]
IS
NOT
NULL
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
This is so easy！但是假如您希望从包含Type字段的某表中根据Type分别随机筛选N条记录并将结果合并成一张表，您可能会像下面这样写：
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
SELECT TOP N [Id],[Name],[Comment] FROM [Product2]
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE1
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE2
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE3
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE4
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE5
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE6
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE7
'
ORDER
BY
NEWID
()
在查询分析器中执行如上语句会报错,这个问题起初会令您觉得UNION在这方面似乎有点软弱，难道UNION和ORDER BY就不能共存吗？当然可以，下面的代码或许能实现与上面代码希望实现的相同功能：
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE1
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE2
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE3
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE4
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE5
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE6
'
ORDER
BY
NEWID
()
UNION
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE7
'
ORDER
BY
NEWID
()
<!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE1
'
ORDER
BY
NEWID
())
AS
[
Product1
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE2
'
ORDER
BY
NEWID
())
AS
[
Product2
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE3
'
ORDER
BY
NEWID
())
AS
[
Product3
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE4
'
ORDER
BY
NEWID
())
AS
[
Product4
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE5
'
ORDER
BY
NEWID
())
AS
[
Product5
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE6
'
ORDER
BY
NEWID
())
AS
[
Product6
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE7
'
ORDER
BY
NEWID
())
AS
[
Product7
]
代码看起来有些繁琐，或许针对这个问题有更见简洁明快的方法，我在这里也只是抛砖引玉，希望各位大侠能相处更为经典的代码，待续~
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE1
'
ORDER
BY
NEWID
())
AS
[
Product1
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE2
'
ORDER
BY
NEWID
())
AS
[
Product2
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE3
'
ORDER
BY
NEWID
())
AS
[
Product3
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE4
'
ORDER
BY
NEWID
())
AS
[
Product4
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE5
'
ORDER
BY
NEWID
())
AS
[
Product5
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE6
'
ORDER
BY
NEWID
())
AS
[
Product6
]
UNION
SELECT
*
FROM
(
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product
]
WHERE
[
Type
]
=
'
TYPE7
'
ORDER
BY
NEWID
())
AS
[
Product7
]
346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
