简学SQL中的自定义排序

创建一个测试用的表test3，记录的数据包含城市名称，人口数量，城市GDP

create table test3 (City char(20),Population double(10,2),GDP double(10,2));

插入相关数据：

insert into test3 values ('北京',2171.70,35371.30);
insert into test3 values ('上海',2418.33,38155.32);
insert into test3 values ('广州',1530.59,23628.60);
insert into test3 values ('天津',1556.87,14104.28);
insert into test3 values ('重庆',3048.43,23605.77);

> select * from test3;
+--------+------------+----------+
| City   | Population | GDP      |
+--------+------------+----------+
| 北京   |    2171.70 | 35371.30 |
| 上海   |    2418.33 | 38155.32 |
| 广州   |    1530.59 | 23628.60 |
| 天津   |    1556.87 | 14104.28 |
| 重庆   |    3048.43 | 23605.77 |
+--------+------------+----------+
5 rows in set (0.04 sec)

方法一  ORDER BY CASE WHEN
SELECT * FROM test3
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
SELECT a.City,a.Population,a.GDP FROM
(
SELECT 1 Num,* FROM test3 WHERE City='北京'
UNION ALL
SELECT 2 Num,* FROM test3 WHERE City='天津'
UNION ALL
SELECT 3 Num,* FROM test3 WHERE City='上海'
UNION ALL
SELECT 4 Num,* FROM test3 WHERE City='重庆'
UNION ALL
SELECT 5 Num,* FROM test3 WHERE City='广州'
) a
ORDER BY a.Num;

方法三 创建临时表
create table temp (Num int,City char(20));

insert into temp values (1,'北京');
insert into temp values (2,'天津');
insert into temp values (3,'上海');
insert into temp values (4,'重庆');
insert into temp values (5,'广州');
 
SELECT a.* FROM test3 a
JOIN temp b ON a.City=b.City
ORDER BY b.Num;