SQL ORDER BY多列 [英] SQL ORDER BY multiple columns

问题描述

我想按两列对我的产品表进行排序:prod_price和prod_name.

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

这里的排序方式如何?我认为它首先由prod_price发生，然后由prod_name发生.另外，以上查询与此查询有何不同:

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_name;

我的产品表如下:

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

解决方案

在ORDER BY中的排序由第一列完成，然后由指定语句中的每个其他列完成.

例如，考虑以下数据:

Column1    Column2
=======    =======
1          Smith
2          Jones
1          Anderson
3          Andrews

查询

SELECT Column1, Column2 FROM thedata ORDER BY Column1, Column2

首先要按Column1

中的所有值排序

，然后按Column2对列进行排序以产生以下结果:

Column1    Column2
=======    =======
1          Anderson
1          Smith
2          Jones
3          Andrews

换句话说，首先按Column1顺序对数据进行排序，然后按第二列的顺序对每个子集(以1为值的Column1行)进行排序.

您发布的两个语句之间的区别在于，第一个语句中的行将首先按prod_price(价格顺序，从最低到最高)进行排序，然后再按名称顺序(即如果两个项目具有相同的价格，名称中具有较低alpha值的名称将首先列出)，而第二个名称将仅按名称顺序排序(这意味着价格将根据prod_name顺序显示，而不考虑价格).

I want to sort my products table by two columns: prod_price and 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_price and then by prod_name. Also, how is the above query different from this one:

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

解决方案

Sorting in an ORDER BY is done by the first column, and then by each additional column in the specified statement.

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

and then sort the columns by Column2 to produce this:

Column1    Column2
=======    =======
1          Anderson
1          Smith
2          Jones
3          Andrews

In other words, the data is first sorted in Column1 order, and then each subset (Column1 rows that have 1 as their value) are sorted in order of the second column.

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_name without regard for price).

这篇关于SQL ORDER BY多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！