按某一列排序

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)