ROW_NUMBER() 窗口函数 / 分析函数

MySQL中，row_number() 函数 用来分区的，使用它 为返回的结果集中的每一行 生成一个序列号（为行分配序号），第一个数字以1开头。

主要用来计数的，也便于用此函数针对全部字段进行排序去重的，例如：CTE、存储过程中使用。

一、数据去重

mysql中去重，给指定数据排序去重一般会有4种类：

• group by 针对单行
• inner join
• select distinct 针对单行（一行中有一个字段不同，就认为两行内容是不重复的）
• row_number() 针对全部字段

二、ROW_NUMBER()语法

ROW_NUMBER() OVER ( [分区定义] [排序定义] )

ROW_NUMBER() OVER ( [PARTITION BY ,[{,}…]] [ORDER BY [ASC|DESC],[{,}…]] )

参数说明

• Partition By
          将行分成更小的集合，是可选的，如果省略它，则将整个结果视为一个分区。
• expression
          分区字段（参与分区列的列表），可使用逗号(,)分隔多个表达式
• Order By
          设置行的顺序，是可选的

返回值说明

• 返回当前行所在的分区内的序号，默认从1开始。

三、示例

3.1 演示数据准备

SELECT
  id,TeamCode,UserName,Deleted
FROM
  tb_userinfo
ORDER BY 
  UserName DESC;

效果展示

Id	TeamCode	UserName	Deleted
UI036	10020001	黄子韬	0
UI300	10030001	黄子韬	0
UI200	10020001	鲜芋	0
UI235	ZR01ZR001	鲜芋	0
UI357	ZR01ZR001	鲜芋	1
UI355	ZR01ZR001	鲜芋	1
UI077	10040001	鲜芋	0

• Id - ID，主键。
• TeamCode - 项目组code
• UserName - 用户名
• Deleted - 是否删除

3.2 演示1 为每行分配序号

SELECT
  id,TeamCode,UserName,Deleted,
  ROW_NUMBER () OVER () AS `row`
FROM
  tb_userinfo
ORDER BY 
  UserName DESC;

效果展示

Id	TeamCode	UserName	Deleted	row
UI036	10020001	黄子韬	0	1
UI300	10030001	黄子韬	0	2
UI200	10020001	鲜芋	0	3
UI235	ZR01ZR001	鲜芋	0	4
UI357	ZR01ZR001	鲜芋	1	5
UI355	ZR01ZR001	鲜芋	1	6
UI077	10040001	鲜芋	0	7

3.3 演示2 删除重复行（去重）

按UserName分区分配序号，已删除在前 未删除在后

-- ROW_NUMBER() OVER (  [分区定义]    [排序定义] )

SELECT
  id,TeamCode,UserName,Deleted,
  ROW_NUMBER ()OVER (
        PARTITION BY UserName   -- 分区字段
        ORDER BY Deleted       -- 排序字段
       )AS `row`
FROM
  tb_userinfo

效果展示

Id	TeamCode	UserName	Deleted	row
UI077	10040001	鲜芋	0	1
UI200	10020001	鲜芋	0	2
UI235	ZR01ZR001	鲜芋	0	3
UI355	ZR01ZR001	鲜芋	1	4
UI357	ZR01ZR001	鲜芋	1	5
UI036	10020001	黄子韬	0	1
UI300	10030001	黄子韬	0	2

看实现效果，我们可以看出来，根据用户名进行分区，未离职的优先展示，会给每个小结果集的每行，生成序列号 从1开始。
这样在多表关联的时候，取 row = 1 的即可。(这里可能不太明显，因为在这里使用ordery会方便，后面会讲解它和order by的区别)

3.4 适用场景

• 为每行分配序列号
• 删除重复行（去重）
• 找到每组的前几行
• 序列号从几开始…从几结束（分页）

-- 1. 分页，类似于筛选了
SELECT
 * 
FROM
       ( SELECT
        id,TeamCode,UserName,Deleted,
        ROW_NUMBER ()OVER (
                PARTITION BY UserName   -- 分区字段
                ORDER BY Deleted       -- 排序字段
            )AS row1
        FROM
        tb_userinfo ) t
WHERE 
    row1 BETWEEN 1 and 2

-- 2. 找到每组的前几行，我这里是以序号进行查找
SELECT
 * 
FROM
       ( SELECT
        id,TeamCode,UserName,Deleted,
        ROW_NUMBER ()OVER (
                PARTITION BY UserName   -- 分区字段
                ORDER BY Deleted       -- 排序字段
            )AS row1
        FROM
        tb_userinfo ) t
WHERE 
    row1 <=3  -- 与上方差异在条件，书写格式基本一致

效果展示

1. 分页，类似于筛选了

Id	TeamCode	UserName	Deleted	row1
UI077	10040001	鲜芋	0	1
UI200	10020001	鲜芋	0	2
UI036	10020001	黄子韬	0	1
UI300	10030001	黄子韬	0	2

2. 找到每组（每个分区）的前3行

Id	TeamCode	UserName	Deleted	row1
UI077	10040001	鲜芋	0	1
UI200	10020001	鲜芋	0	2
UI235	ZR01ZR001	鲜芋	0	3
UI036	10020001	黄子韬	0	1
UI300	10030001	黄子韬	0	2

四、ROW_NUMBER() 与 ORDER BY 区别

以上的你让order by来实现可以吗，也可以的。你有时间的话用order by去实现以上功能，也能实现。

并且你去看sql ROW_NUMBER() 与 ORDER BY性能（可以使用 Explain，查看sql执行的详细信息），会发现两者都几乎是一模一样。

4.1 使用order by 实现删除重复行（去重）

SELECT
  *
FROM (
      SELECT
        id,TeamCode,UserName,Deleted
      FROM
        tb_userinfo 
      ORDER BY 
        UserName , Deleted) t
GROUP BY 
  UserName

效果展示

Id	TeamCode	UserName	Deleted
UI077	10040001	鲜芋	0
UI036	10020001	黄子韬	0

4.2 row_number()与group by使用区别

row_number() 是给这个区 进行排序，再每个结果集中每行赋给序号。主要是用来计数的。

order by 是整体的 所有的进行排序。

• 如果要select出不在group by语句中的项目，则应该使用row_number()
• 如果需要select的项目全在group by中，则使用group by语句

五、推荐内容

---

• MySql . Concat_WS()函数
  通过分隔符，将2个及多个字段元素顺序拼接在一起，返回拼接后的字符串。

---

• MySql . Field()函数
  返回一个指定的值 在给定的列表中的 索引（排序、数据优先级）。

---

• MySql . CEIL()函数
• 返回大于或等于制定数字的最小整数值。sql取余数…

---

• EXPLAIN 详解 type\rows
• 查看你的sql执行的详细信息，以供开发人员针对性进行优化…