SQL自定义排序的三种方法

SQL自定义排序的三种方法

在SQL查询中，有时我们需要按照特定的顺序输出结果，而不是基于任何列的自然排序。例如，我们可能需要按照"北京、天津、上海、重庆……"这样的顺序来显示城市数据。本文将介绍三种实现这种自定义排序的方法。

示例表结构

我们使用一张名为Citys的表作为示例，表中包含城市名称、人口和GDP等信息。表中的数据是随机录入的。

目标输出

我们需要按照特定顺序输出城市数据，这个顺序既不是人口多少，也不是GDP多少，更不是城市名称的拼音首字母，而是完全按照我们自己的意愿进行排序。

方法一：使用ORDER BY CASE WHEN

这种方法通过在ORDER BY子句中使用CASE WHEN语句，将文本转化为可排序的数字来进行间接排序。

SELECT * FROM Citys
ORDER BY 
CASE WHEN City='北京' THEN 1      
     WHEN City='天津' THEN 2
     WHEN City='上海' THEN 3
     WHEN City='重庆' THEN 4
     WHEN City='广州' THEN 5
END  

这种方法适用于简单的查询场景，但在复杂场景中使用起来会受到限制。

方法二：使用UNION ALL

这种方法通过UNION ALL子句为每个城市添加一个自定义的排序序号，然后对这个序号进行排序。

SELECT a.City,a.Population,a.GDP FROM
(
SELECT 1 Num,* FROM Citys WHERE City='北京'
UNION ALL
SELECT 2 Num,* FROM Citys WHERE City='天津'
UNION ALL
SELECT 3 Num,* FROM Citys WHERE City='上海'
UNION ALL
SELECT 4 Num,* FROM Citys WHERE City='重庆'
UNION ALL
SELECT 5 Num,* FROM Citys WHERE City='广州'
) a
ORDER BY a.Num  

这种方法虽然容易理解，但代码会变得比较复杂，通常用于需要复杂查询且需要自定义排序的场景。

方法三：创建临时表

这种方法通过创建一个临时表，将自定义的排序顺序存储在临时表中，然后在查询时通过JOIN关联这个临时表。

首先创建临时表：

CREATE TEMPORARY TABLE Temp (Num INT, City VARCHAR(255));
INSERT INTO Temp VALUES (1, '北京'), (2, '天津'), (3, '上海'), (4, '重庆'), (5, '广州');

然后使用JOIN进行排序：

SELECT a.* FROM Citys a
JOIN Temp b ON a.City=b.City
ORDER BY b.Num  

这种方法代码量较少，且易于维护，只需要更新临时表中的顺序即可适应各种不同的场景。