本文探讨了SQL中UNION关键字的使用方法，并详细解释了如何结合ORDER BY进行随机数据抽取，通过实例展示了如何避免常见错误。
http://www.cnblogs.com/yinzhenzhixin/archive/2009/01/07/1371064.html
在SQL语句中，UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表)，简单实例如下：
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
SELECT
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product2
]
上面的代码可以实现将从Product1和Product2两张表合并成一个表，如果您只是希望合并两张表中符合特定条件的记录抑或是合并两张表各自的前N条记录，那么您的代码可能会像下面这样写：
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
SELECT
TOP
N
[
Id
]
,
[
Name
]
,
[
Comment
]
FROM
[
Product1
]
UNION
SELECT TOP N [Id],[Name],[Comment] FROM [Product2]
This is so easy！但是假如您希望从包含Type字段的某表中根据Type分别随机筛选N条记录并将结果合并成一张表，您可能会像下面这样写：
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就不能共存吗？当然可以，下面的代码或许能实现与上面代码希望实现的相同功能：
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
]
代码看起来有些繁琐，或许针对这个问题有更见简洁明快的方法，我在这里也只是抛砖引玉，希望各位大侠能相处更为经典的代码，待续~
扫一扫
586
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
