PostgreSQL中使用ROW_NUMBER() OVER函数的基本用法

语法：

ROW_NUMBER ( ) OVER ( PARTITION BY COLUMN ORDER BY COLUMN)

一些基本的pgsql命令：

  1. 使用 \l 用于查看已经存在的数据库： 
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 sovzn     | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)
  1. 使用 \c + 数据库名 来进入数据库 
postgres=# \c sovzn
您现在已经连接到数据库 "sovzn",用户 "postgres".
  1. 我们可以使用 \d 命令来查看当前数据库下的所有表格： 
sovzn=# \d
                关联列表
 架构模式 |   名称   |  类型  |  拥有者
----------+----------+--------+----------
 public   | employee | 数据表 | postgres
(1 行记录)
  1. \d tablename 查看表格信息
sovzn=# \d employee
                数据表 "public.employee"
  栏位  |     类型      | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
 empid  | integer       |           |          |
 deptid | integer       |           |          |
 salary | numeric(10,2) |           |          |

测试

创建表员工表（ empid：员工ID， deptid：所在部门ID， salary：工资）

sovzn=# select * from employee;
 empid | deptid |  salary
-------+--------+----------
     1 |     10 |  5500.00
     2 |     10 |  4500.00
     3 |     20 |  1900.00
     4 |     20 |  4800.00
     5 |     40 |  6500.00
     6 |     40 | 14500.00
     7 |     40 | 44500.00
     8 |     50 |  6500.00
     9 |     50 |  7500.00
(9 行记录)

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER( ) OVER (ORDER BY salary DESC) 是先把 salary 列降序，再为降序以后的每条salary记录返回一个序号:

# -----默认序号列名：

sovzn=# select *,ROW_NUMBER() OVER(ORDER BY salary DESC) from employee;
 empid | deptid |  salary  | row_number
-------+--------+----------+------------
     7 |     40 | 44500.00 |          1
     6 |     40 | 14500.00 |          2
     9 |     50 |  7500.00 |          3
     5 |     40 |  6500.00 |          4
     8 |     50 |  6500.00 |          5
     1 |     10 |  5500.00 |          6
     4 |     20 |  4800.00 |          7
     2 |     10 |  4500.00 |          8
     3 |     20 |  1900.00 |          9
(9 行记录)

# -----自定义序号列名：

sovzn=# select *,(ROW_NUMBER() OVER(ORDER BY salary DESC))rw from employee;
 empid | deptid |  salary  | rw
-------+--------+----------+----
     7 |     40 | 44500.00 |  1
     6 |     40 | 14500.00 |  2
     9 |     50 |  7500.00 |  3
     5 |     40 |  6500.00 |  4
     8 |     50 |  6500.00 |  5
     1 |     10 |  5500.00 |  6
     4 |     20 |  4800.00 |  7
     2 |     10 |  4500.00 |  8
     3 |     20 |  1900.00 |  9
(9 行记录)

根据部门分组，每个部门中按照员工工资倒序排序：

sovzn=# SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rw FROM employee;
 empid | deptid |  salary  | rw
-------+--------+----------+----
     1 |     10 |  5500.00 |  1
     2 |     10 |  4500.00 |  2
     4 |     20 |  4800.00 |  1
     3 |     20 |  1900.00 |  2
     7 |     40 | 44500.00 |  1
     6 |     40 | 14500.00 |  2
     5 |     40 |  6500.00 |  3
     9 |     50 |  7500.00 |  1
     8 |     50 |  6500.00 |  2
(9 行记录)

PostgreSQL 中Having的用法

HAVING 子句可以让我们筛选分组后的各组数据。

WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

HAVING 子句必须放置于 GROUP BY 子句后面，ORDER BY 子句前面，下面是 HAVING 子句在 SELECT 语句中基础语法

栗子：

sovzn=# select * from employee;
 empid | deptid |  salary
-------+--------+----------
     1 |     10 |  5500.00
     2 |     10 |  4500.00
     3 |     20 |  1900.00
     4 |     20 |  4800.00
     5 |     40 |  6500.00
     6 |     40 | 14500.00
     7 |     40 | 44500.00
     8 |     50 |  6500.00
     9 |     50 |  7500.00
(9 行记录)

按照部门分组，并返回每组数据大于两条的部门：

sovzn=# select deptid from employee group by deptid having count(deptid) >= 2;
 deptid
--------
     40
     10
     50
     20
(4 行记录)

筛选出部门号大于10的，然后按照部门号分组，返回每组数据大于2的并顺序排序

sovzn=# select deptid from employee where deptid > 10 group by deptid having count(deptid) >= 2 order by deptid;
 deptid
--------
     20
     40
     50
(3 行记录)