Combined use of union and orderby _ MySQL

Combine union and orderby using bitsCN.com

Combined use of union and order

In SQL statements, the UNION keyword is used to merge multiple parallel query results (tables) into one result (table). A simple example is as follows:

SELECT [Id],[Name],[Comment] FROM [Product1]UNIONSELECT [Id],[Name],[Comment] FROM [Product2]

The code above combines two tables, Product1 and Product2, into one table, if you only want to merge the records that meet the specified conditions in the two tables or merge the first N records of the two tables, your code may be written as follows:

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! However, if you want to randomly filter N records from a table containing the Type field and combine the results into a table, you may write the following statement as follows:

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()

If you execute the preceding statement in the query analyzer, an error is returned. this problem may initially make you feel that UNION seems a little weak in this regard. can UNION and order by not coexist? Of course, the following code may implement the same functions as the above code:

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