union与order by合并使用_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

union与order by合并使用_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:28 PM

Keywords

bitsCN.com

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]=&#39;TYPE1&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE2&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE3&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE4&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE5&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE6&#39; ORDER BY NEWID()UNIONSELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE7&#39; ORDER BY NEWID()

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

SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE1&#39; ORDER BY NEWID()) AS [Product1]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE2&#39; ORDER BY NEWID()) AS [Product2]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE3&#39; ORDER BY NEWID()) AS [Product3]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE4&#39; ORDER BY NEWID()) AS [Product4]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE5&#39; ORDER BY NEWID()) AS [Product5]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE6&#39; ORDER BY NEWID()) AS [Product6]    UNION    SELECT * FROM        (SELECT TOP N [Id],[Name],[Comment] FROM [Product] WHERE [Type]=&#39;TYPE7&#39; ORDER BY NEWID()) AS [Product7]

bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles