299
汽車大全
PostgreSQL Oracle 兼容性 - order by INT(select位置 position)
標簽
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
最後更新：2017-10-28 23:34:08