postgresql多列综合排序

一、需求

最近做项目遇到一个需求：对数据按照更新时间和创建时间进行综合排序，即对数据的操作时间进行排序，但是数据表中没有操作时间这个字段，需要根据更新时间和创建时间进行处理。更新时间存在时，按照更新时间排序，更新时间不存在时，使用创建时间排序，最后更新时间和创建时间一起排序。用数据举例说明：
原始数据:

想要的排序结果：

二、postgresql 排序语法

SELECT column_list
    FROM table_name
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, `sort_expression2` [ASC | DESC] [NULLS { FIRST | LAST }] ...]

说明：

1. sort_expression 可以是列名，也可以是列的表达式.如可以将不同列相加后排序：

SELECT a, b FROM table1 ORDER BY a + b, c;

2. ASC | DESC 是可选的，按升序或者降序排列结果集。默认是 ASC。
3. NULLS FIRST 和 NULLS LAST 选项可以决定在排序操作中在 null 值之前还是之后。默认情况下，空值大于任何非空值；也就是说，DESC 排序默认是 NULLS FIRST，否则为 NULLS LAST。

三、使用排序 SQL 尝试获取得到想要的排序结果

1. 先按照 modify_time 和 create_time 列降序排列

sql：

select id, name, creator, create_time, modifier, modify_time from tb_exam order by modify_time desc, create_time desc;

运行结果：

分析：
从运行结果看出 modify_time 为 null 的数据排列在前，不符合需求。

2. 改变 null 值的位置

sql：

select id, name, creator, create_time, modifier, modify_time from tb_exam order by modify_time desc nulls last, create_time desc;

运行结果：

分析：
modify_time 为 null 的数据虽然在后面了，但排序结果并不符合要求。没有达到修改时间和创建时间综合排序的效果。

由此可见，使用基本的排序语法达不到两列综合排序的效果，可使用 case when 实现自定义排序规则。

3. 自定义排序规则

sql：

select id, name, creator, create_time, modifier, modify_time from tb_exam order by
case
	when modify_time is null then create_time
	when modify_time < create_time then create_time
	else modify_time
end
desc;

运行结果：

分析：
从运行结果可知，该 sql 的排序结果符合需求，实现了 modify_time 和 create_time 的综合排序。

结论：可使用 case when 在一定程度上实现自定义排序规则，实现多列数据综合排序。