数据排序
更新时间 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)
