在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]

代码看起来有些繁琐，或许针对这个问题有更见简洁明快的方法，我在这里也只是抛砖引玉，希望各位大侠能相处更为经典的代码，待续~

转发于http://www.cnblogs.com/yinzhenzhixin/archive/2009/01/07/1371064.html

最新文章

1. git入门札记
2. ASP.Net的导出Excel的快速方法，DataTable导出Excel（亲测，非原创）
3. 【转】mysql安装图解
4. sh脚本学习之: sh脚本 、sed、awk
5. Fresco 源码分析(三) Fresco服务端处理(2) Producer具体实现的内容
6. Nginx_地址重写(rewrite)_日志管理(log_format)_压缩输出_Nginx设定限速_Nginx设置反向代理及反向代理缓存
7. Linux C 文件与目录4 将缓冲区数据写入磁盘
8. HeadFirst Jsp 07 (使用 jsp)
9. 试图从数据库 ‘UFData_001_2003' 中提取的逻辑页 (1:10720) 属于对象 '0'，而非对象 'syscolumns'
10. Devexpress GridControl中combobox级联显示 z
11. css实现两端对齐~
12. jquery精简选项卡
13. HDU4712+随机算法
14. Android app性能测试小结（7个性能指标）
15. 禁被ping 软件漏洞升级
16. 【bzoj4567 scoi2016】 背单词
17. iOS中使用iCloud一些需要注意的地方(Xcode7.2)
18. .NET 下 模拟数组越界
19. Redis Cluster搭建高可用Redis服务器集群
20. table-layout引起的探索——fixed和auto的区别

热门文章

1. Document
2. Type mismatch: cannot convert from java.sql.PreparedStatement to com.mysql.jdbc.PreparedStatement
3. idea IntelliJ IDEA 2016.2破解
4. java.lang.IllegalStateException: Failed to read Class-Path attribute from manifest of jar file:/XXX
5. Oracle数据库，用户的创建及表的创建
6. python验证登录
7. 【Effective Java】11、同步访问共享的可变数据
8. Java经典实例：使用ChoiceFormat来格式化复数
9. .Net中的并行编程-5.流水线模型实战
10. 高性能javascript学习笔记系列(4) -算法和流程控制