mysql查询合并
发布网友
发布时间：2022-05-07 16:41
共4个回答
懂视网
时间：2022-05-07 21:02
union与order by合并使用
在SQL语句中，UNION关键字多用来将并列的多组查询结果(表)合并成一个结果(表)，简单实例如下：
SELECT [Id],[Name],[Comment] FROM [Product1]UNIONSELECT [Id],[Name],[Comment] FROM [Product2]
上面的代码可以实现将从Product1和Product2两张表合并成一个表，如果您只是希望合并两张表中符合特定条件的记录抑或是合并两张表各自的前N条记录，那么您的代码可能会像下面这样写：
SELECT [Id],[Name],[Comment] FROM [Product1] WHERELEN([Name]) > 5UNIONSELECT [Id],[Name],[Comment] FROM [Product2] WHERE [Id] IN (11,20) AND [Comment] IS NOT NULLSELECT TOP N [Id],[Name],[Comment] FROM [Product1]UNIONSELECT 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()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE2' ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE3' ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE4' ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE5' ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]='TYPE6' ORDER BY NEWID()UNIONSELECT 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]
bitsCN.com
热心网友
时间：2022-05-07 18:10
你的问题是 union all 与 order by 不能一起使用
需要 改成：
1、
select * from (select * from mallbuilder_proct where flash_sale=1 and FROM_UNIXTIME(end_time,'%Y-%m-%d %H:%m:%s')>NOW() ORDER BY flash_sale_date ASC) a
union all
select * from (select * from mallbuilder_proct where flash_sale=1 and
FROM_UNIXTIME(end_time,'%Y-%m-%d %H:%m:%s')>NOW() ORDER BY
flash_sale_date ASC) b
或者
select * from (
select * from mallbuilder_proct where flash_sale=1 and
FROM_UNIXTIME(end_time,'%Y-%m-%d %H:%m:%s')>NOW()
union all
select * from mallbuilder_proct where flash_sale=1 and
FROM_UNIXTIME(end_time,'%Y-%m-%d %H:%m:%s')>NOW()
)ORDER BY
flash_sale_date ASC
还有，不明白的是，为什么要两个一样的语句 union all，没猜错的话， where 条件 ＝2
热心网友
时间：2022-05-07 19:28
热心网友
时间：2022-05-07 21:03
