SQL如何使用UNION连接ORDER BY语句_Bearshouse

http://blog.sina.com.cn/u/1436983063

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SQL如何使用UNION连接ORDER BY语句

(2016-02-23 16:22:18)

分类： SQL

在SQL语句中，UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表)，简单实例如下：

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT [Id],[Name],[Comment] FROM [Product1]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT [Id],[Name],[Comment] FROM [Product2]

上面的代码可以实现将从Product1和Product2两张表合并成一个表，如果您只是希望合并两张表中符合特定条件的记录抑或是合并两张表各自的前N条记录，那么您的代码可能会像下面这样写：

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT [Id],[Name],[Comment] FROM [Product1] WHERE LEN([Name]) > 5
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT [Id],[Name],[Comment] FROM [Product2] WHERE [Id] IN (11,20) AND [Comment] IS NOT NULL

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product1]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product2]

This is so easy！但是假如您希望从包含Type字段的某表中根据Type分别随机筛选N条记录并将结果合并成一张表，您可能会像下面这样写：

http://common.cnblogs.com/images/copycode.gifBY语句" />

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE1' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE2' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE3' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE4' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE5' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE6' ORDER BY NEWID()
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE7' ORDER BY NEWID()

http://common.cnblogs.com/images/copycode.gifBY语句" />

在查询分析器中执行如上语句会报错,这个问题起初会令您觉得UNION在这方面似乎有点软弱，难道UNION和ORDER BY就不能共存吗？当然可以，下面的代码或许能实现与上面代码希望实现的相同功能：

http://common.cnblogs.com/images/copycode.gifBY语句" />

http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE1' ORDER BY NEWID()) AS [Product1]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE2' ORDER BY NEWID()) AS [Product2]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE3' ORDER BY NEWID()) AS [Product3]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE4' ORDER BY NEWID()) AS [Product4]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE5' ORDER BY NEWID()) AS [Product5]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE6' ORDER BY NEWID()) AS [Product6]
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    UNION
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />    SELECT * FROM
http://www.cnblogs.com/Images/OutliningIndicators/None.gifBY语句" />        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE7' ORDER BY NEWID()) AS [Product7]

http://common.cnblogs.com/images/copycode.gifBY语句" />

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

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：SQL Server 中 RAISERROR 的用法

后一篇：WINS和DNS两者之间的区别

新浪BLOG意见反馈留言板　欢迎批评指正