表
# select * from jinbo.persons;
id | lastname | firstname | address | city
----+----------+-----------+---------------+----------
3 | Snow | Jon | Oxford Street | London
1 | Adams | Jonn | Oxford Street | London
2 | Bush | George | Fifth Avenue | New York
4 | Rose | Leslie | |
5 | Rose | Alice | |
(5 rows)
排序要求
比如展示要把address为null的放在最前边,其他排序依赖address 地址出现的次数排序,sql 如下:
# select address, count(1) from jinbo.persons group by address order by ( case when address is null then 1 else 0 end ) desc;
address | count
---------------+-------
| 2
Fifth Avenue | 1
Oxford Street | 2