标签

PostgreSQL , order by , 排序 , 字段 , 表达式 , 位置

---

背景

在一些Oracle的用户中，会问到是否支持order by INT这样的查询语法。它是什么意思呢？实际上就是按查询子句的第几个表达式进行排序。

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm

ORDER [ SIBLINGS ] BY  
{ expr | position | c_alias }  
[ ASC | DESC ]  
[ NULLS FIRST | NULLS LAST ]  
  [, { expr | position | c_alias }  
     [ ASC | DESC ]  
     [ NULLS FIRST | NULLS LAST ]  
  ]...  

按 表达式、列别名、select位置排序。

PostgreSQL也支持这种语法。

PostgreSQL order by支持

PostgreSQL 天然支持 order by [字段、表达式、位置]。

    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]  
  
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.  

例子

1、按别名排序

postgres=# explain select relpages as ooo,* from pg_class order by ooo;  
                            QUERY PLAN                               
-------------------------------------------------------------------  
 Sort  (cost=71.81..73.32 rows=602 width=737)  
   Sort Key: relpages  
   ->  Seq Scan on pg_class  (cost=0.00..44.02 rows=602 width=737)  
(3 rows)  

2、按SELECT中的位置排序

postgres=# explain select relpages,reltuples,relname from pg_class order by 2 limit 1;  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Limit  (cost=47.03..47.03 rows=1 width=72)  
   ->  Sort  (cost=47.03..48.54 rows=602 width=72)  
         Sort Key: reltuples  
         ->  Seq Scan on pg_class  (cost=0.00..44.02 rows=602 width=72)  
(4 rows)  

3、按表达式排序

postgres=# explain select relpages,reltuples,relname from pg_class order by reltuples+relpages limit 1;  
                               QUERY PLAN                                 
------------------------------------------------------------------------  
 Limit  (cost=50.04..50.04 rows=1 width=80)  
   ->  Sort  (cost=50.04..51.55 rows=602 width=80)  
         Sort Key: ((reltuples + (relpages)::double precision))  
         ->  Seq Scan on pg_class  (cost=0.00..47.03 rows=602 width=80)  
(4 rows)  

参考

https://www.postgresql.org/docs/10/static/sql-select.html

https://www.postgresql.org/docs/10/static/sql-expressions.html#sql-syntax-collate-exprs