db2中row

#db2中row| 来源: 网络整理| 查看: 265

db2中有个row_number()函数，是很多开发的最爱(手动狗头)，今天简单学习一下。

row_number()函数有什么用？

它产生一个伪列(pseudocolumn)，直意就是行号，即结果集中的第几行。从1开始，连续的整数。

row_number()函数怎么用？

常用的方法如下，其中最重要的功能是partition by

row_number() over() row_number() over(partition by xx) row_number() over(order by yy) row_number() over(partition by xx order by yy )

示例

为了简单起见，创建一张只有1个字段的表，并插入一些记录，如下： $ db2 "create table t1(id int)" db2DB20000I The SQL command completed successfully. $ db2 "insert into t1 values(1),(2),(3),(4),(1),(1),(4)" DB20000I The SQL command completed successfully.

示例1：查询结果中，每条记录都显示行号：

$ db2 "select id, row_number() over() as row_num from t1" ID ROW_NUM ----------- -------------------- 1 1 2 2 3 3 4 4 1 5 1 6 4 7 7 record(s) selected.

示例2：按照ID分组，结果中的行号为每一组组内的编号：

$ db2 "select id, row_number() over(partition by id) as row_num from t1" ID ROW_NUM ----------- -------------------- 1 1 1 2 1 3 2 1 3 1 4 1 4 2 7 record(s) selected.

示例3：按照ID排序：

$ db2 "select id, row_number() over(order by id) as row_num from t1" ID ROW_NUM ----------- -------------------- 1 1 1 2 1 3 2 4 3 5 4 6 4 7 7 record(s) selected.

示例4：先按照ID分组，组内按照ID排序（我这里排序的字段和分组的字段相同，没有实际上的意义）：

$ db2 "select id, row_number() over(partition by id order by id ) as row_num from t1" ID ROW_NUM ----------- -------------------- 1 1 1 2 1 3 2 1 3 1 4 1 4 2 7 record(s) selected.

示例5：只显示表中重复的记录（ID相同认为是重复），示例4中对ID进行了分组并排序，因此只要输出row_num>1的记录便是重复的记录

$ db2 "select * from (select id, row_number() over(partition by id order by id ) as row_num from t1) where row_num > 1" ID ROW_NUM ----------- -------------------- 1 2 1 3 4 2 3 record(s) selected.

示例6：删除重复的记录（ID相同认为是重复），示例5已经给出了重复的记录，现在只要加一个delete就行

$ db2 "delete from (select * from (select id, row_number() over(partition by id order by id ) as row_num from t1) where row_num > 1)" DB20000I The SQL command completed successfully. $ db2 "select * from t1" ID ----------- 1 2 3 4 4 record(s) selected.

示例7：只显示结果集中前N行/第M~N行/最后N行，可用于分页展示：

db2 "select id from (select id, row_number() over() as row_num from t1) where row_num =5"

示例8：再举一个实际一点的例子，以sample数据库中自带的employee表为例子，想要查询每个部门中工资最高的员工的员工号、姓名、部门、工资，可以使用row_number函数，按照部门分组，按照工资降序排序，然后只取每组的第1条数据（row_number=1）：

$ db2 "describe table employee" Data type Column Column name schema Data type name Length Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ EMPNO SYSIBM CHARACTER 6 0 No FIRSTNME SYSIBM VARCHAR 12 0 No MIDINIT SYSIBM CHARACTER 1 0 Yes LASTNAME SYSIBM VARCHAR 15 0 No WORKDEPT SYSIBM CHARACTER 3 0 Yes PHONENO SYSIBM CHARACTER 4 0 Yes HIREDATE SYSIBM DATE 4 0 Yes JOB SYSIBM CHARACTER 8 0 Yes EDLEVEL SYSIBM SMALLINT 2 0 No SEX SYSIBM CHARACTER 1 0 Yes BIRTHDATE SYSIBM DATE 4 0 Yes SALARY SYSIBM DECIMAL 9 2 Yes BONUS SYSIBM DECIMAL 9 2 Yes COMM SYSIBM DECIMAL 9 2 Yes 14 record(s) selected. $ db2 "select EMPNO, FIRSTNME, WORKDEPT, salary from (select EMPNO, FIRSTNME, WORKDEPT, salary, row_number() over(partition by WORKDEPT order by SALARY desc) as row_num from employee) where row_num=1" EMPNO FIRSTNME WORKDEPT SALARY ------ ------------ -------- ----------- 000010 CHRISTINE A00 152750.00 000020 MICHAEL B01 94250.00 000030 SALLY C01 98250.00 000060 IRVING D11 72250.00 000070 EVA D21 96170.00 000050 JOHN E01 80175.00 000090 EILEEN E11 89750.00 000100 THEODORE E21 86150.00 8 record(s) selected.

附：DB2还有一种更简单的获取行号的方法，即直接使用ROWNUM，前提是设置了DB2_COMPATIBILITY_VECTOR=01变量并重启了DB2数据库，示例如下

$ db2set DB2_COMPATIBILITY_VECTOR=01 $ db2stop force $ db2start $ db2 connect to sample $ db2 "select * from t1 where ROWNUM between 1 and 3" ID ----------- 1 2 3 3 record(s) selected. $ db2 "select * from t1 where ROWNUM between 1 and 10" ID ----------- 1 2 3 4 1 1 4 7 record(s) selected.

部分参考资料：

ROWNUM pseudocolumn

【本文地址】

db2中row

db2中row

今日新闻

推荐新闻