union与order by合并使用

页面导航:首页 > 数据库 > Mysql > union与order by合并使用

union与order by合并使用

来源: 作者: 时间:2016-02-04 09:03 【

union与order by合并使用在SQL语句中,UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表),简单实例如下:SELECT [Id],[Name],[Comment] FROM [Product1]UNIONSELECT [Id],[Name],[Comment] F
union与order by合并使用
 
在SQL语句中,UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表),简单实例如下:
 
SELECT [Id],[Name],[Comment] FROM [Product1]
UNION
SELECT [Id],[Name],[Comment] FROM [Product2]

 

 
上面的代码可以实现将从Product1和Product2两张表合并成一个表,如果您只是希望合并两张表中符合特定条件的记录抑或是合并两张表各自的前N条记录,那么您的代码可能会像下面这样写:
 
SELECT [Id],[Name],[Comment] FROM [Product1] WHERELEN([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]

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<