索码理

V1

2022/09/17阅读:20主题:凝夜紫

ORDER BY排序一篇就够了

MySQL 排序一篇就够了

工作中常常会使用ORDER BY进行排序,了解ORDER BY多种排序方式是非常有必要的。

ORDER BY 排序可以分为静态排序和动态排序,当然这个静态和动态是我自己取的名字,其他地方找不到。

ORDER BY 语法

ORDER BY column1 ASC/DESC, column2 ASC/DESC.....

column代表一个列字段,ORDER BY可以允许多个字段进行排序,当字段后面显式说明是升序(ASC)或者降序(DESC)时,默认是ASC。

实践出真知

实践准备

先准备一张orders表

CREATE TABLE `orders` (
  `id` bigint(20unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint(2NOT NULL DEFAULT '1' COMMENT '状态',
  `is_deleted` bit(1NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
ENGINE=InnoDB

表里存放着9条数据

静态排序

所谓静态排序就是ORDER BY后面排序的字段是固定不变的,不会根据排序字段的值的变化而变化。

举个栗子:

SELECT * FROM orders ORDER BY id

上面这条SQL语句不会根据id的值变化字段id随之变化。这类排序就是静态排序。

ORDER BY后面排序字段可以跟1个或者多个,1个排序字段的就是单字段排序,多个字段的就是多字段排序。单字段排序比较简单,上面的SQL就是单字段排序。

多字段排序中要特别注意一点的是有时选择了多个字段进行排序,但并没有达到期望的结果,因为多字段排序是分组排序。

举个栗子说明一下:

列1:

SELECT * FROM orders ORDER BY id ,  create_date DESC  

运行结果如下:

上面这条SQL语句看似是按id升序,create_date降序,通过运行结果可以看到实则create_date排序并没有起作用。

在ORDER BY排序中是根据第一个字段先排序,然后在第一个字段的基础上再排序,如果第一个字段的值是不重复的,排序后后面的字段就失去了作用了。

列2:

SELECT * FROM orders ORDER BY `status`  ,  create_date DESC  

运行结果:

列2的SQL中我们把id字段排序换成了status字段排序,由于status字段的值是重复的,从结果中我们可以看到在status字段排序完成之后,create_date字段的排序就起作用了,就按照我们指定的DESC进行排序了。

所以多字段进行排序,实则是分组排序,排序的第一个字段的值如果不重复,后面的排序字段将失效。第一个字段的值如果重复,第二个字段将会在第一个字段重复组内进行排序,第三个字段会在前两个字段排好序的基础上排序,以此类推。

动态排序

动态排序和静态排序相反(这就是一句废话Ծ‸Ծ)。所谓动态排序就是ORDER BY后面的排序字段会根据值的变化而变化。工作中应该会常常用到,比如在a=1时根据b字段进行排序,在a=2时根据c字段进行排序。这种的就称之为动态排序。

动态排序一般都会搭配着判断条件进行排序的,比如 Case when、if等。下面就分别使用Case和if条件判断进行排序。

  • CASE WHEN 动态排序
SELECT
 *
FROM
 orders
ORDER BY `status` DESC
 CASE
WHEN `status` = 2 THEN
 create_date
END DESC,
 CASE
WHEN `status` = 4 THEN
 update_date
END

运行结果如下:

当status=2时根据create_date降序,当status=4时根据update_date升序。从结果中也可以看到达到了我们想要的效果。

  • IF 动态排序 IF 动态排序跟使用CASE WHEN排序类似,也是对字段进行判断。
SELECT
 *
FROM
 orders
ORDER BY
IF (`status` = 3'0''1'),
 `status`,
 create_date DESC

运行结果:

在这段SQL中,运行结果可以分为两部分status=3是一部分,其余是另一部分,此时IF判断其实是附加了一个隐藏字段,status=3时返回0,否则返回1,然后再根据返回结果进行升序排序。从下面这段SQL看会更清晰。

SELECT
 id,
 `status`,
IF (`status` = 3'0''1') hidden_column,
 create_date,
 update_date,
 is_deleted
FROM
 orders
ORDER BY
IF (`status` = 3'0''1'),
 `status`,
 create_date DESC

就是对hidden_column进行排序之后,再对status进行升序排序,接着再对create_date降序排序,结果也是一样的。

ORDER BY为什么支持动态排序呢?是因为ORDER BY执行顺序优先级比较低,它是在获取到结果之后才进行排序的,在获取到结果集之后可以进行一些其他操作,就像我们从数据库获取结果集对其进行排序一样,所以它可以支持动态排序。除此之后ORDER BY后面可以搭配其他函数或者查询条件使用,比如LIKE、CONCAT、IN或者一些其他的函数都可以,感兴趣的自己可以试一试,下面就拿CONCAT做个测试。

比如可以将两个字段拼接后排序:

SELECT
 `status` , id ,CONCAT(`status` , id
FROM orders
ORDER BY
 CONCAT(`status`idDESC

结果:

总结

  1. mysql 中排序默认是按照升序ASC进行排序的
  2. 在多字段进行排序时,实则是分组排序。多字段排序会将每个分组划分为一个个小的单元,在这一个个小的单元内再进行排序。
  3. ORDER BY的执行顺序优先级相对较低,是在查询结果出来以后再进行排序。
  4. ORDER BY可以搭配函数或者一些查询条件使用。

分类:

后端

标签:

数据库

作者介绍

索码理
V1