如何规范化数据库表数据，在不修改SQL查询的前提下得到期望输出

1. course表存在严重数据冗余，不符合第三范式设计要求：同一门课程对应多条重复的id和title记录，属于典型的重复存储问题。
2. role字段为普通字符串类型，字符串字典序中L（ASCII码76）大于I（ASCII码73），按role DESC降序排序时Learner自然排在Instructor前面，和需求相悖。

完全不需要修改现有查询语句，仅需按以下三步调整表结构和数据即可：

步骤1：清理course表冗余数据 #

删除course表中重复的课程条目，仅保留每个课程的唯一记录，调整后course表数据如下：

=> select * from course;
 id | title 
----+-------
  1 | si106
  2 | si110
  3 | si206
(3 rows)

步骤2：修正roster表的course_id外键关联 #

将同一课程下所有选课记录的course_id统一指向course表对应课程的唯一id，调整后roster表数据如下：

=> select * from roster;                                                                                                             
 id | student_id | course_id |    role    
----+------------+-----------+------------
 16 |          1 |         1 | Instructor
 17 |          2 |         1 | Learner
 18 |          3 |         1 | Learner
 19 |          4 |         1 | Learner
 20 |          5 |         1 | Learner
 21 |          6 |         2 | Instructor
 22 |          7 |         2 | Learner
 23 |          8 |         2 | Learner
 24 |          9 |         2 | Learner
 25 |         10 |         2 | Learner
 26 |         11 |         3 | Instructor
 27 |         12 |         3 | Learner
 28 |         13 |         3 | Learner
 29 |         14 |         3 | Learner
 30 |         15 |         3 | Learner
(15 rows)

步骤3：调整role字段类型为枚举实现正确排序 #

将roster表的role字段修改为枚举类型，枚举值定义顺序设置为('Learner', 'Instructor')，数据库对枚举类型的排序会遵循定义时的顺序而非字符串字典序：

-- PostgreSQL 环境修改示例
ALTER TABLE roster ALTER COLUMN role TYPE ENUM('Learner', 'Instructor') USING role::ENUM('Learner', 'Instructor');

按此定义Instructor的排序权重要高于Learner，按role DESC排序时Instructor会自动排在前面，且输出的角色名称和需求完全一致。

调整完成后执行原有查询即可得到期望输出结果。

内容的提问来源于stack exchange，提问作者Neil Cardin