|
|
51CTO旗下网站
|
|
移动端

除了会排序,你对ORDER BY的用法可能一无所知!

小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。今天给大家分享一些你可能不知道的ORDER BY用法。

作者:佚名来源:数据库开发|2019-12-24 15:16

小伙伴们在进行SQL排序时,都能很自然的使用到ORDER BY。不管是默认ASC的升序,还是DESC降序,几乎都是信手拈来。

今天给大家分享一些你可能不知道的ORDER BY用法。

一、ORDER BY返回的是游标而不是集合

SQL的理论其实是集合论,常见的类似求数据的交集、并集、差集都可以使用集合的思维来求解。

集合中的行之间没有预先定义的顺序,它只是成员的一种逻辑组合,成员之间的顺序无关紧要。

如下图,每一个括号里的内容就是一条记录,在没排序前,他们都是随机分布在集合中。

Student(ID,Name,Age)

Student集合

但是对于带有排序作用的ORDER BY子句的查询,它返回的是一个对象,其中的行按特定的顺序组织在一起,我们把这种对象称为游标。

如下图,经过对Student表的ID进行ORDER BY排序后,Student表变成了有序对象,也就是我们上面说的游标。

Student(ID,Name,Age)

Student对象

二、ORDER BY子句是唯一能重用列别名的一步

注:markdown对代码块的语法是开始和结束行都要添加:```,其中 ` 为windows键盘左上角那

这里涉及SQL语句的语法顺序和执行顺序了,我们常见的SQL语法顺序如下: 

  1. SELECT DISTINCT  <Top Num> <select list>FROM [left_table]<join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>WITH <CUBE | RollUP>HAVING <having_condition>ORDER BY <order_by_list>  

而数据库引擎在执行SQL语句并不是从SELECT开始执行,而是从FROM开始,具体执行顺序如下(关键字前面的数字代表SQL执行的顺序步骤): 

  1. (8)SELECT (9)DISTINCT  (11)<Top Num> <select list>  
  2.     (1)FROM [left_table]  
  3.     (3)<join_type> JOIN <right_table>  
  4.     (2)        ON <join_condition>  
  5.     (4)WHERE <where_condition>  
  6.     (5)GROUP BY <group_by_list>  
  7.     (6)WITH <CUBE | RollUP>  
  8.     (7)HAVING <having_condition>  
  9.     (10)ORDER BY <order_by_list>  

从上面可以看到SELECT在HAVING后才开始执行,这个时候SELECT后面列的别名只对后续的步骤生效,而对SELECT前面的步骤是无效的。所以如果你在WHERE,GROUP BY,或HAVING后面使用列的别名均会报错。

我们举例测试一下。

示例表Customers结构及数据如下:

1、WHERE后面不使用别名的情况

  1. SELECT   
  2. 姓名 AS Name,  
  3. 地址 AS Address,  
  4. 城市 AS City  
  5. FROM Customers  
  6. WHERE 城市='广州' 

结果如下:

2、WHERE后面使用列别名的情况

  1. SELECT   
  2. 姓名 AS Name,  
  3. 地址 AS Address,  
  4. 城市 AS City  
  5. FROM Customers  
  6. WHERE City='广州' 

执行结果如下:

从返回的消息中我们可以看到,重命名后的City并不能被WHERE识别,所以才会报“列名'City'无效”的提示。

其他关键字大家也可以使用上述方法进行测试,下面我们测试GROUP BY和HAVING后面使用列别名的情况。

3、测试GROUP BY后使用列别名

  1. SELECT   
  2. 城市 AS City  
  3. FROM Customers  
  4. GROUP BY City 

结果如下:

4、测试HAVING后使用列别名

  1. SELECT   
  2. 城市 AS City  
  3. FROM Customers  
  4. GROUP BY 城市  
  5. HAVING COUNT(City)>

结果如下:

5、测试ORDER BY后面使用列别名

  1. SELECT   
  2. 姓名 AS Name,  
  3. 地址 AS Address,  
  4. 城市 AS City  
  5. FROM Customers  
  6. ORDER BY City 

结果如下:

从上面的几个测试示例的结果中,可以得出我们的结论是正确的:ORDER BY子句是唯一能重用列别名的一步。

三、谨慎使用ORDER BY 后面接数字的方式来进行排序

有些小伙伴为了图省事,喜欢在ORDER BY后面写数字,具体示例如下:

  1. SELECT   
  2. 姓名 AS Name,  
  3. 地址 AS Address,  
  4. 城市 AS City  
  5. FROM Customers  
  6. ORDER BY 1,2,3 

结果如下:

这样写的结果,针对当前的查询是正确没有问题的,ORDER BY后面的数字1,2,3分别代表SELECT后面的第1,第2,第3个字段(也就是Name,Address,City)。

可是当查询的列发生改变,忘了修改ORDER BY列表。特别是当查询语句很长时,要找到ORDER BY与SELECT列表中的哪个列相对应会非常困难。

例如:

  1. SELECT   
  2. 客户ID AS ID,  
  3. 姓名 AS Name,  
  4. 地址 AS Address,  
  5. 城市 AS City  
  6. FROM Customers  
  7. ORDER BY 1,2,3 

由于增加了一列“客户ID”,原本的题意还是对Name,Address,City排序,但是因为使用了ORDER BY加数字,排序后的结果如下:

得到的结果并不是我们想要的,所以请慎用ORDER BY加数字,尽量使用ORDER BY加列名或列别名。

四、表达式不能使用ORDER BY排序

表表达式包括视图,内联表值函数,派生表(子查询)和公用表表达式(CTE)。

例如下面的视图是无效的

  1. CREATE VIEW V_Customers AS  
  2. SELECT   
  3. 客户ID AS ID,  
  4. 姓名 AS Name,  
  5. 地址 AS Address,  
  6. 城市 AS City  
  7. FROM Customers  
  8. ORDER BY ID,Name,Address 

结果如下:

这个错误是不是很熟悉?因为很多小伙伴经常喜欢在视图或子查询里面加ORDER BY,然后一执行就会报这个错。

根本原因不敢妄加断定,因为搜寻了很多文献资料也没给出一个具体的说法。

这里我猜测是因为视图,内联表值函数,派生表(子查询)和公用表表达式(CTE)等返回的结果还需要进一步的去使用,加了ORDER BY进行排序是多此一举,反而会浪费系统资源。所以数据库的开发者不希望大家使用这样不规范操作。

所以下次就不要在表表达式里添加ORDER BY了。

五、T-SQL中表表达式加了TOP可以使用ORDER BY

我们从第四点的报错信息中可以看到:在另外还指定了 TOP、OFFSET 或 FOR XML是可以使用ORDER BY的。

这又是为什么呢?

我们还是先举个栗子给大家看一下

  1. SELECT   
  2. 客户ID AS ID,  
  3. 姓名 AS Name,  
  4. 地址 AS Address,  
  5. 城市 AS City  
  6. FROM  
  7. (SELECT TOP 3 *  
  8. FROM Customers  
  9. ORDER BY 城市) Customers  
  10. ORDER BY ID,Name,Address 

结果如下:

因为T-SQL中带有ORDER BY的表表达式加了TOP后返回的是一个没有固定顺序的表。因此,在这种情况下,ORDER BY子句只是为TOP选项定义逻辑顺序,就是下面这个逻辑子句

  1. SELECT TOP 3 *  
  2. FROM Customers  
  3. ORDER BY 城市 

结果如下:

而不保证结果集的排列顺序,因为表表达式外面至少还有一层才是我们最终需要的结果集。

这里的ORDER BY只对当前的子查询生效,到了主查询是不起作用的。必须在主查询末尾继续添加一个ORDER BY子句才能对结果集生效,就像我们例子中写的那样。

除非逻辑要求,一般情况下并不推荐大家这样巧妙的避开子查询中不能使用ORDER BY的限制。

以上就是有关ORDER BY的一些用法,你学会了吗?有不明白或疑问的地方,欢迎在底下留言。

【编辑推荐】

  1. MySQL数据库ORDER BY优化总结(为排序使用索引)
  2. 我们是怎样打造一款分布式数据库的
  3. MySQL索引八大法则之上篇
  4. 详解Oracle数据库LRU算法:LRU链、脏块与脏LRU链
  5. 面试官:MySQL的可重复读级别能解决幻读问题吗?
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Python应用场景实战手册

Python应用场景实战手册

Python应用场景实战手册
共3章 | KaliArch

118人订阅学习

一步到位玩儿透Ansible

一步到位玩儿透Ansible

Ansible
共17章 | 骏马金龙1

190人订阅学习

云架构师修炼手册

云架构师修炼手册

云架构师的必备技能
共3章 | Allen在路上

132人订阅学习

读 书 +更多

JAVA并发编程实践

本书既能够成为读者的理论支持,又可以作为构建可靠的、可伸缩的、可维护的并发程序的技术支持。本书并不仅仅提供并发API的清单及其机制,...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微