关于PostgreSQL 行排序的实例解析-巨人网络通讯

主页 > 知识库 > 关于PostgreSQL 行排序的实例解析

关于PostgreSQL 行排序的实例解析

在查询生成输出表之后，也就是在处理完选择列表之后，你还可以对输出表进行排序。

如果没有排序，那么行将以不可预测的顺序返回(实际顺序将取决于扫描和连接规划类型和在磁盘上的顺序，

但是肯定不能依赖这些东西)。确定的顺序只能在明确地使用了排序步骤之后才能保证。

ORDER BY子句用于声明排序顺序:

SELECT _select_list_
  FROM _table_expression_
  ORDER BY _sort_expression1_ [ASC | DESC] [NULLS { FIRST | LAST }]
       [, `_sort_expression2_` [ASC | DESC] [NULLS { FIRST | LAST }] ...]

sort_expression 是任何可用于选择列表的表达式，可以将不同列相加减乘除后排序，例如：

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

如果指定了多个排序表达式，那么仅在前面的表达式排序相等的情况下才使用后面的表达式做进一步排序。

每个表达式都可以跟一个可选的ASC(升序，默认) 或DESC(降序)以设置排序方向。升序先输出小的数值，这里的"小"是以lt;操作符的角度定义的。

类似的是，降序是以gt;操作符来判断的。

NULLS FIRST和NULLS LAST选项可以决定在排序操作中在 non-null 值之前还是之后。

默认情况下，空值大于任何非空值；也就是说，DESC 排序默认是NULLS FIRST，否则为NULLS LAST。

注意，排序选项对于每个排序列是相对独立的。例如ORDER BY x, y DESC 意思是说ORDER BY x ASC, y DESC，

不同于ORDER BY x DESC, y DESC。

一个_sort_expression_也可以是字段名或字段编号，如:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

都按照第一个字段进行排序。

需要注意的是，输出字段名必须是独立的(不允许在表达式中使用)。

比如，下面的语句是错误的：

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;     -- 错误的

这样的限制主要是为了避免歧义。另外，如果某个排序表达式能够同时匹配输出字段名和表表达式中的字段名，也会导致歧义(此时使用输出字段名)。

当然，这种情况仅在你使用了AS 重命名输出字段并且恰好与其它表的字段同名的时候才会发生。

ORDER BY可以应用于UNION, INTERSECT,EXCEPT 组合的计算结果，

不过在这种情况下，只允许按照字段的名字或编号进行排序，而不允许按照表达式进行排序。

Notes

[1] 事实上，PostgreSQL使用默认的B-tree操作符类为表达式的数据类型确定ASC和DESC排序顺序。

一般来说，数据类型将被转换为适合于 lt;和 gt;操作符进行排序。但是对于用户自定义的数据类型可以不必如此。

补充：postgresql多列综合排序

一、需求

最近做项目遇到一个需求：对数据按照更新时间和创建时间进行综合排序，即对数据的操作时间进行排序，但是数据表中没有操作时间这个字段，需要根据更新时间和创建时间进行处理。

更新时间存在时，按照更新时间排序，更新时间不存在时，使用创建时间排序，最后更新时间和创建时间一起排序。

用数据举例说明：

原始数据:

想要的排序结果：

二、postgresql 排序语法

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

说明：

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

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

ASC | DESC 是可选的，按升序或者降序排列结果集。默认是 ASC。

NULLS FIRST 和 NULLS LAST 选项可以决定在排序操作中在 null 值之前还是之后。默认情况下，空值大于任何非空值；也就是说，DESC 排序默认是 NULLS FIRST，否则为 NULLS LAST。

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

先按照 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 的数据排列在前，不符合需求。

改变 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 实现自定义排序规则。

自定义排序规则

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;

运行结果：