mysql orderitems_【Mysql】教程全解（三）ORDER BY 排序

最新推荐文章于 2022-06-18 00:43:25 发布

西瓜桑葚

最新推荐文章于 2022-06-18 00:43:25 发布

阅读量918

点赞数

文章标签： mysql orderitems

本文链接：https://blog.csdn.net/weixin_35915385/article/details/113212196

版权

3.1 order by

当使用SELECT语句查询表中的数据时，结果集不按任何顺序进行排序。要对结果集进行排序，请使用ORDER BY子句。

ORDER BY子句允许：对单个列或多个列排序结果集。

按升序或降序对不同列的结果集进行排序。

下面说明了ORDER BY子句的语法：

SELECT column1, column2,...FROM tbl

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

SQL

ASC表示升序，DESC表示降序。默认情况下，如果不明确指定ASC或DESC，ORDER BY子句会按照升序对结果集进行排序。

下面我们来学习和练习一些使用ORDER BY子句的例子。

2. MySQL ORDER BY示例

请参见示例数据库(yiibaidb)中的customers表，customers表的结构如下所示 -

132264488_1_20180506124230441

以下查询从customers表中查询联系人，并按contactLastname升序对联系人进行排序。

SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname;

----------------- ------------------

| contactLastname | contactFirstname |

----------------- ------------------

| Accorti | Paolo |

| Altagar,G M | Raanan |

| Andersen | Mel |

| Anton | Carmen |

| Ashworth | Rachel |

| Barajas | Miguel |

| Benitez | Violeta |

| Bennett | Helen |

| Berglund | Christina |

| Bergulfsen | Jonas |

| Bertrand | Marie |

... ....

| Young | Julie |

| Young | Mary |

| Young | Dorothy |

----------------- ------------------

122 rows in set

Shell

如果要按姓氏降序对联系人进行排序，请在ORDER BY子句中的contactLastname列后面指定DESC，如下查询：

SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname DESC;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname DESC;

----------------- ------------------

| contactLastname | contactFirstname |

----------------- ------------------

| Young | Jeff |

| Young | Julie |

| Young | Mary |

... ...

| Anton | Carmen |

| Andersen | Mel |

| Altagar,G M | Raanan |

| Accorti | Paolo |

----------------- ------------------

122 rows in set

Shell

如果要按姓氏按降序和名字按升序排序联系人，请在相应列中分别指定DESC和ASC，如下所示：

SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname DESC,

contactFirstname ASC;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT

contactLastname,

contactFirstname

FROM

customers

ORDER BY

contactLastname DESC,

contactFirstname ASC;

----------------- ------------------

| contactLastname | contactFirstname |

----------------- ------------------

| Young | Dorothy |

| Young | Jeff |

| Young | Julie |

| Young | Mary |

| Yoshido | Juri |

| Walker | Brydey |

| Victorino | Wendy |

| Urs | Braun |

| Tseng | Jerry |

... ...

| Brown | Julie |

| Brown | William |

| Bertrand | Marie |

| Bergulfsen | Jonas |

| Berglund | Christina |

| Bennett | Helen |

| Benitez | Violeta |

| Barajas | Miguel |

| Ashworth | Rachel |

| Anton | Carmen |

| Andersen | Mel |

| Altagar,G M | Raanan |

| Accorti | Paolo |

----------------- ------------------

122 rows in set

Shell

在上面的查询中，ORDER BY子句首先按照contactLastname列降序对结果集进行排序，然后按照contactFirstname列升序对排序结果集进行排序，以生成最终结果集。

MySQL ORDER BY按表达式排序示例

ORDER BY子句还允许您根据表达式对结果集进行排序。请参阅以下orderdetails表结构 -

132264488_2_20180506124230566

以下查询从orderdetails表中选择订单行记录项目。它计算每个订单项的小计，并根据订单编号，订单行号(orderLineNumber)和小计(quantityOrdered * priceEach)对结果集进行排序。

SELECT

ordernumber,

orderlinenumber,

quantityOrdered * priceEach

FROM

orderdetails

ORDER BY

ordernumber,

orderLineNumber,

quantityOrdered * priceEach;

SQL

执行上面语句，总共有 2996 行结果集，以下是部分结果集片断 -

132264488_3_20180506124230660

为了使查询更易于阅读，可以按列别名进行排序，方法如下：

SELECT

ordernumber,

orderlinenumber,

quantityOrdered * priceEach AS subtotal

FROM

orderdetails

ORDER BY

ordernumber,

orderLineNumber,

subtotal;

SQL

执行上面语句，总共有 2996 行结果集，以下是部分结果集片断 -

132264488_4_20180506124230769

上面表达式中，使用subtotal作为表达式quantityOrdered * priceEach的列别名，并根据小计别名(subtotal)对结果集进行排序。

MySQL ORDER BY与自定义排序顺序

ORDER BY子句允许使用FIELD()函数为列中的值定义自己的自定义排序顺序。

看看下面 orders表的结构如下所示 -

132264488_5_20180506124230847

例如，如果要按以下顺序基于以下状态的值对订单进行排序：In Process

On Hold

Cancelled

Resolved

Disputed

Shipped

可以使用FIELD()函数将这些值映射到数值列表，并使用数字进行排序; 请参阅以下查询：

SELECT

orderNumber, statusFROM

orders

ORDER BY FIELD(status,

'In Process',

'On Hold',

'Cancelled',

'Resolved',

'Disputed',

'Shipped');

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT

orderNumber, status

FROM

orders

ORDER BY FIELD(status,

'In Process',

'On Hold',

'Cancelled',

'Resolved',

'Disputed',

'Shipped');

------------- ------------

| orderNumber | status |

------------- ------------

| 10420 | In Process |

| 10421 | In Process |

| 10422 | In Process |

| 10423 | In Process |

| 10424 | In Process |

| 10425 | In Process |

| 10334 | On Hold |

| 10401 | On Hold |

| 10407 | On Hold |

| 10414 | On Hold |

| 10167 | Cancelled |

| 10179 | Cancelled |

| 10248 | Cancelled |

| 10253 | Cancelled |

| 10260 | Cancelled |

| 10262 | Cancelled |

| 10164 | Resolved |

| 10327 | Resolved |

... ...

| 10413 | Shipped |

| 10416 | Shipped |

| 10418 | Shipped |

| 10419 | Shipped |

------------- ------------

326 rows in set

3 MySQL GROUP BY与聚合函数

可使用聚合函数来执行一组行的计算并返回单个值。 GROUP BY子句通常与聚合函数一起使用以执行计算每个分组并返回单个值。

例如，如果想知道每个状态中的订单数，可以使用COUNT函数与GROUP BY子句查询语句，如下所示：

SELECT

status, COUNT(*) AS total_number

FROM

orders

GROUP BY status;

SQL

执行上面查询语句，得到以下结果 -

132264488_6_20180506124230957

132264488_7_2018050612423119

要按状态获取所有订单的总金额，可以使用orderdetails表连接orders表，并使用SUM函数计算总金额。请参阅以下查询：

SELECT

status, SUM(quantityOrdered * priceEach) AS amount

FROM

orders

INNER JOIN

orderdetails USING (orderNumber)GROUP BY status;

SQL

执行上面查询，得到以下结果 -

132264488_8_2018050612423166

类似地，以下查询返回订单号和每个订单的总金额。

SELECT

orderNumber,

SUM(quantityOrdered * priceEach) AS total

FROM

orderdetails

GROUP BY orderNumber;

SQL

执行上面查询，得到以下结果 -

132264488_9_20180506124231144

2.3 MySQL GROUP BY用表达式示例

除了列之外，可以按表达式对行进行分组。以下查询获取每年的总销售额。

SELECT

YEAR(orderDate) AS year,

SUM(quantityOrdered * priceEach) AS total

FROM

orders

INNER JOIN

orderdetails USING (orderNumber)WHERE

status = 'Shipped'GROUP BY YEAR(orderDate);

SQL

执行上面查询，得到以下结果 -

132264488_10_20180506124231238

在这个例子中，我们使用YEAR函数从订单日期(orderDate)中提取年份数据。只包括已发货(Shipped)状态的订单。请注意，SELECT子句中出现的表达式必须与GROUP BY子句中的相同。

4. MySQL GROUP BY与HAVING子句

可使用HAVING子句过滤GROUP BY子句返回的分组。以下查询使用HAVING子句来选择2013年以后的年销售总额。

SELECT

YEAR(orderDate) AS year,

SUM(quantityOrdered * priceEach) AS total

FROM

orders

INNER JOIN

orderdetails USING (orderNumber)WHERE

status = 'Shipped'GROUP BY year

HAVING year > 2013;

SQL

执行上面查询，得到以下结果 -

132264488_11_20180506124231300

3.2 order by子句与自然语言排序

在本教程中，您将使用ORDER BY子句了解MySQL中的各种自然排序技术。

下面让我们使用一个示例数据来开始学习自然排序技术。

假设我们有一个items的表，其中包含两列：id和item_no。使用以下CREATE TABLE语句创建items表，如下：

CREATE TABLE IF NOT EXISTS items (

id INT AUTO_INCREMENT PRIMARY KEY,

item_no VARCHAR(255) NOT NULL);

SQL

我们使用INSERT语句将一些数据插入到items表中：

INSERT INTO items(item_no)VALUES ('1'),

('1C'),

('10Z'),

('2A'),

('2'),

('3C'),

('20D');

SQL

当我们查询选择数据并按item_no排序时，得到以下结果：

132264488_12_20180506124231378

这不是我们的预期的结果，我们想要看到的结果如下：

132264488_13_20180506124231457

这被称为自然排序。不幸的是，MySQL不提供任何内置的自然排序语法或函数。 ORDER BY子句以线性方式排序字符串，即从第一个字符开始的每个字符一次。

为了克服这个问题，首先我们将item_no列分成两列：prefix 和 suffix。 prefix列存储item_no的数字部分，suffix列存储字母部分。然后根据这些列对数据进行排序，如下所示：

SELECT

CONCAT(prefix, suffix)FROM

items

ORDER BY prefix , suffix;

SQL

查询首先对数据进行数字排序，并按字母顺序对数据进行排序。我们就得到预期的结果。

这个解决方案的缺点是必须在插入或更新之前将item_no值分成两部分。此外，当查询数据时，必须将这两列组合成一列。

如果item_no数据格式相当标准，则可以使用以下查询执行自然排序，而无需更改表的结构。

SELECT

item_no

FROM

items

ORDER BY CAST(item_no AS UNSIGNED) , item_no;

SQL

执行上面查询语句，得到以下结果 -

SELECT

item_no

FROM

items

ORDER BY CAST(item_no AS UNSIGNED) , item_no;

Shell

在这个查询中，首先使用类型转换将item_no数据转换为无符号整数。其次，使用ORDER BY子句对数字进行数字排序，然后按字母顺序排列。

下面来看看经常要处理的另一个常见的数据。

TRUNCATE TABLE items;INSERT INTO items(item_no)VALUES('A-1'),

('A-2'),

('A-3'),

('A-4'),

('A-5'),

('A-10'),

('A-11'),

('A-20'),

('A-30');

SQL

排序后的预期结果如下：

132264488_14_20180506124231519

为了得到上面这个结果，可以使用LENGTH函数。请注意，LENGTH函数返回字符串的长度。这个做法是首先对item_no数据进行排序，然后按列值排序，如以下查询：

SELECT

item_no

FROM

items

ORDER BY LENGTH(item_no) , item_no;

SQL

执行上面查询，得到以下结果 -

mysql> SELECT

item_no

FROM

items

ORDER BY LENGTH(item_no) , item_no;

---------

| item_no |

---------

| A-1 |

| A-2 |

| A-3 |

| A-4 |

| A-5 |

| A-10 |

| A-11 |

| A-20 |

| A-30 |

---------

9 rows in set

Shell

如下所看到数据就是自然排序的。

但是，如果所有上述解决方案都不适合。则需要在应用程序层执行自然排序。一些语言支持自然排序功能，例如，PHP提供了使用自然排序算法对数组进行排序的natsort()函数。

3.3 Having 子句

在SELECT语句中使用HAVING子句来指定一组行或聚合的过滤条件。

HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。请注意，HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

MySQL HAVING子句示例

让我们举一些使用HAVING子句的例子来看看它是如何工作。我们将使用示例数据库(yiibaidb)中的orderdetails表进行演示。

132264488_15_20180506124231597

可以使用GROUP BY子句来获取订单号，查看每个订单销售的商品数量和每个销售总额：

SELECT

ordernumber,

SUM(quantityOrdered) AS itemsCount,

SUM(priceeach*quantityOrdered) AS total

FROM

orderdetails

GROUP BY ordernumber;

SQL

执行上面查询语句，得到以下结果(部分) -

132264488_16_20180506124231691

现在，可以通过使用HAVING子句查询(过滤)哪些订单的总销售额大于55000，如下所示：

SELECT

ordernumber,

SUM(quantityOrdered) AS itemsCount,

SUM(priceeach*quantityOrdered) AS totalFROM

orderdetailsGROUP BY ordernumberHAVING total > 55000;

SQL

执行上面查询语句，得到以下结果 -

132264488_17_20180506124231800

可以使用逻辑运算符(如OR和AND)在HAVING子句中构造复杂过滤条件。假设您想查找哪些订单的总销售额大于50000，并且包含超过600个项目，则可以使用以下查询：

SELECT

ordernumber,

SUM(quantityOrdered) AS itemsCount,

SUM(priceeach*quantityOrdered) AS total

FROM

orderdetails

GROUP BY ordernumber

HAVING total > 50000 AND itemsCount > 600;

SQL

执行上面查询语句，得到以下结果 -

132264488_18_20180506124231894

假设您想查找所有已发货(status='Shiped')的订单和总销售额大于55000的订单，可以使用INNER JOIN子句将orders表与orderdetails表一起使用，并在status列和总金额(total)列上应用条件，如以下查询所示：

执行上面查询，得到以下结果 -

132264488_19_201805061242323

HAVING子句仅在使用GROUP BY子句生成高级报告的输出时才有用。例如，您可以使用HAVING子句来回答统计问题，例如在本月，本季度或今年总销售额超过10000的订单。

西瓜桑葚

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql orderitems_【Mysql】教程全解（三）ORDER BY 排序

3.1 order by当使用SELECT语句查询表中的数据时，结果集不按任何顺序进行排序。要对结果集进行排序，请使用ORDER BY子句。ORDER BY子句允许：对单个列或多个列排序结果集。按升序或降序对不同列的结果集进行排序。下面说明了ORDER BY子句的语法：SELECT column1, column2,...FROM tblORDER BY column1 [ASC|DESC], ...
复制链接

扫一扫