union与order by合并使用 - mysql数据库栏目

union与order by合并使用 - mysql数据库栏目 - 自学p

来源：自学PHP网时间：2015-04-16 10:51 作者：阅读:次

[导读] 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]

mysql中获取下一个自增长的id值 - mysql数据库栏目

MySQL事务学习-->隔离级别 - mysql数据库栏目 - 自

子栏目

union与order by合并使用 - mysql数据库栏目 - 自学p

最新评论

添加评论

更多文章推荐

添加评论