标签
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
