數據排序
更新時間 2025-02-05 09:36:28
最近更新時間: 2025-02-05 09:36:28
分享文章
本文為您介紹如何在使用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 1
null 值記錄排在最前面。
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)
