MySQL SQL ORDER BY 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15628277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL ORDER BY multiple columns
提问by user2201462
I want to sort my products table by two columns: prod_priceand prod_name.
我想按两列对我的产品表进行排序：prod_price和prod_name。
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
How is the sorting done here? I think it happens first by prod_priceand then by prod_name. Also, how is the above query different from this one:
这里的排序是如何进行的？我认为它首先发生prod_price，然后由prod_name。此外，上述查询与此查询有何不同：
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_name;
My products table is as follows:
我的产品表如下：
CREATE TABLE Products
(
prod_id char(10) NOT NULL ,
vend_id char(10) NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL
);
回答by Ken White
Sorting in an ORDER BYis done by the first column, and then by each additional column in the specified statement.
在 an 中的排序ORDER BY由第一列完成，然后由指定语句中的每个附加列完成。
For instance, consider the following data:
例如，考虑以下数据：
Column1 Column2
======= =======
1 Smith
2 Jones
1 Anderson
3 Andrews
The query
查询
SELECT Column1, Column2 FROM thedata ORDER BY Column1, Column2
would first sort by all of the values in Column1
将首先按中的所有值排序 Column1
and then sort the columns by Column2to produce this:
然后对列进行排序Column2以生成以下内容：
Column1 Column2
======= =======
1 Anderson
1 Smith
2 Jones
3 Andrews
In other words, the data is first sorted in Column1order, and then each subset (Column1rows that have 1as their value) are sorted in order of the second column.
换句话说，数据首先按Column1顺序排序，然后每个子集（Column1具有1值的行）按第二列的顺序排序。
The difference between the two statements you posted is that the rows in the first one would be sorted first by prod_price(price order, from lowest to highest), and then by order of name (meaning that if two items have the same price, the one with the lower alpha value for name would be listed first), while the second would sort in name order only (meaning that prices would appear in order based on the prod_namewithout regard for price).
您发布的两个语句之间的区别在于，第一个中的行将首先按prod_price（价格顺序，从最低到最高）排序，然后按名称顺序排序（意味着如果两个项目具有相同的价格，则具有较低 alpha 值的 name 将首先列出），而第二个将仅按名称顺序排序（意味着价格将根据prod_name不考虑价格的顺序出现）。
回答by Rahul
The results are ordered by the first column, then the second, and so on for as many columns as the ORDER BY clause includes. If you want any results sorted in descending order, your ORDER BY clause must use the DESC keyword directly after the name or the number of the relevant column.
结果按第一列排序，然后是第二列，依此类推，与 ORDER BY 子句包含的列数相同。如果您希望任何结果按降序排序，您的 ORDER BY 子句必须直接在相关列的名称或编号之后使用 DESC 关键字。
Check out this Example
看看这个例子
SELECT first_name, last_name, hire_date, salary
FROM employee
ORDER BY hire_date DESC,last_name ASC;
It will order in succession. Order the Hire_Date first, then LAST_NAME it by Hire_Date .
它将依次订购。首先订购 Hire_Date ，然后按 Hire_Date 订购 LAST_NAME 。
回答by Erwin Brandstetter
Yes, the sorting is different.
是的，排序是不同的。
Items in the ORDER BYlist are applied in order.
Later items only order peers left from the preceding step.
ORDER BY列表中的项目按顺序应用。
后面的项目只对上一步留下的对等点进行排序。
Why don't you just try?
你为什么不试试呢？
回答by Fabian 'Mumbles' Wiesner
It depends on the size of your database.
这取决于您的数据库的大小。
SQL is based on the SET theory: there is no order inherently used when querying a table.
SQL 基于 SET 理论：查询表时没有固有使用的顺序。
So if you were to run the first query, it would first order by product price and then product name, IF there were any duplicates in the price category, say $20 for example, it would then order those duplicates by their names, therefore always maintaining that when you run your query it will always return the same set of result in the same order.
因此，如果您要运行第一个查询，它将首先按产品价格排序，然后按产品名称排序，如果价格类别中有任何重复项，例如 20 美元，它将按名称排序这些重复项，因此始终保持当您运行查询时，它将始终以相同的顺序返回相同的结果集。
If you were to run the second query, it would only order by the name, so if there were two products with the same name (for some odd reason) then they wouldn't have a guaranteed order after you run the query.
如果您要运行第二个查询，它只会按名称排序，因此如果有两个产品具有相同的名称（出于某种奇怪的原因），那么在您运行查询后它们将不会有保证的订单。
回答by kartiikeya
yes,the sorting proceed differently. in first scenario, orders based on column1 and in addition to that process further by sorting colmun2 based on column1 .. in second scenario ,it orders completely based on column 1 only... please proceed with a simple example...u will get quickly..
是的，排序的进行方式不同。在第一种情况下，基于 column1 的订单，此外还通过根据 column1 对 colmun2 进行排序来进一步处理该过程......迅速地..
