创建一个测试用的表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;