SQL中order by的高级用法

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

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

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

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

Student对象

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

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> 

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

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

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

SELECT 
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
WHERE 城市='广州'

SELECT 
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
WHERE City='广州'

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

SELECT 
城市 AS City
FROM Customers
GROUP BY City

结果如下：

4、测试HAVING后使用列别名

SELECT 
城市 AS City
FROM Customers
GROUP BY 城市
HAVING COUNT(City)>1

结果如下：

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

SELECT 
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
ORDER BY City

结果如下：

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

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

结果如下：

例如

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

CREATE VIEW V_Customers AS
SELECT 
客户ID AS ID,
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM Customers
ORDER BY ID,Name,Address

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

SELECT 
客户ID AS ID,
姓名 AS Name,
地址 AS Address,
城市 AS City
FROM
(SELECT TOP 3 *
FROM Customers
ORDER BY 城市) Customers
ORDER BY ID,Name,Address

结果如下：

SELECT TOP 3 *
FROM Customers
ORDER BY 城市

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

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

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

个人公众号：SQL数据库开发