數據排序
更新時間 2025-02-14 10:21:41
最近更新時間: 2025-02-14 10:21:41
分享文章
本文為您介紹如何在使用SELECT語法時進行排序。
數據排序
按某一列排序
teledb=# create table teledb_pg(id int, nickname text); CREATE TABLE teledb=# insert into teledb_pg values(1,'teledb'),(3,'pg'),(1,'hello,pgxc'); COPY 3 teledb=# select * from teledb_pg order by nickname; id | nickname ----+------------ 1 | hello,pgxc 3 | pg 1 | teledb (3 rows)按第一列排序
teledb=# select * from teledb_pg order by 1; id | nickname ----+------------ 1 | teledb 1 | hello,pgxc 3 | pg按ID 升級排序，再按 nickname 降序排序
teledb=# select * from teledb_pg order by id,nickname desc; id | nickname ----+------------ 1 | teledb 1 | hello,pgxc 3 | pg (3 rows)效果與上面的語句一樣。
teledb=# select * from teledb_pg order by 1,2 desc; id | nickname ----+------------ 1 | teledb 1 | hello,pgxc 3 | pg (3 rows)隨機排序
teledb=# select * from teledb_pg order by random(); id | nickname ----+------------ 1 | teledb 3 | pg 1 | hello,pgxc (3 rows) teledb=# select * from teledb_pg order by random(); id | nickname ----+------------ 1 | hello,pgxc 1 | teledb 3 | pg (3 rows)計算排序
teledb=# select * from teledb_pg order by md5(nickname); id | nickname ----+------------ 1 | hello,pgxc 3 | pg 1 | teledb (3 rows)排序也能用子查詢。
teledb=# select * from teledb_pg order by (select id from teledb_pg order by random() limit 1); id | nickname ----+------------ 1 | teledb 1 | hello,pgxc 3 | pg (3 rows)null 值排序結果處理
teledb=# insert into teledb_pg values(4,null); INSERT 0 1null 值記錄排在最前面。
teledb=# select * from teledb_pg order by nickname nulls first; id | nickname ----+------------ 4 | 1 | hello,pgxc 3 | pg 1 | teledb (4 rows)null 值記錄排在最后。
teledb=# select * from teledb_pg order by nickname nulls last; id | nickname ----+------------ 1 | hello,pgxc 3 | pg 1 | teledb 4 | (4 rows)按拼音排序
使用convert 函數實現漢字按拼音進行排序。
teledb=# select * from (values ('張三'), ('李四'),('陳五')) t(myname) order by convert(myname::bytea,'UTF-8','GBK'); myname -------- 陳五 李四 張三 (3 rows)使用convert_to 函數實現漢字按拼音進行排序。
teledb=# select * from (values ('張三'), ('李四'),('陳五')) t(myname) order by convert_to(myname,'GBK'); myname -------- 陳五 李四 張三 (3 rows)通過指定排序規則collact 來實現漢字按拼音進行排序。
teledb=# select * from (values ('張三'), ('李四'),('陳五')) t(myname) order by myname collate "zh_CN.utf8"; myname -------- 陳五 李四 張三 (3 rows)
