最近在写一个查询的sql，要求是如果有result=1的数据，则展示；没有的话，按EXPIRE_DAY倒序排序，获取第一条数据。其中PRODIST_SKU_NUM是可能重复的，但是最终只能返回一个。

表结构大概如下：

create table XXX
(
    ID                     int auto_increment
        primary key,
    CUSTOMER_MANAGE_NUMBER varchar(256) null comment '客户经理工号',
    CUSTOMER_MANAGE_NAME   varchar(256) null comment '客户经理名称',
    PRODIST_SKU_NUM        varchar(64)  not null comment '订购实例ID',
    EXPIRE_DAY             varchar(64)  not null comment '授权结束时间',
    RESULT                 int          null comment '授权结果1-通过、2-驳回、3-超时未反馈、4-RAM账号创建失败',
    STATUS                 varchar(4)   null comment '首次登陆是否确认通过，1-是，0-否，2-确认中,3-已注销'
)

窗口函数语法

注意：mysql中窗口函数在mysql8.0以后支持；oracle中从8.1.6以后开始支持。

函数名() over(partition by <要分组的列> order by <要排序的列> rows between <数据范围>)

其中：

• partition by:用于分组（与聚合函数group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录，而 group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条））
• order by:用于排序
• rows between:限制数据范围

其中，over()里的分组以及排序的执行晚于 where、group by、order by 的执行。

常用函数:

• rank()：每个分区内独立执行。对查询结果进行排序并为每个行分配排名。它可以根据指定的排序列对数据进行排序，并为每个行返回一个唯一的排名值。如果存在并列的情况，RANK()函数会为相同的值分配相同的排名，并在随后的排名中跳过相应的名次。例如，如果有两个并列第一，则前两名的排名都是1，第三个人的排名是31
• dense_rank()：每个分区内独立执行。会为相同的行分配相同的排名，并且下一个排名不会跳过，即如果有两个排名为1的值，接下来的值将会被标记为2，而不是3
• row_number()：每个分区内独立执行。从1开始查询结果集中的每一行分配一个唯一的顺序号（行号）
• 聚合函数
• 偏移函数

语法示例

• 使用ROW_NUMBER() OVER (ORDER BY <列名>)进行排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status,
       ROW_NUMBER() OVER (ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
FROM XXX
WHERE customer_manage_number = ?

• 使用ROW_NUMBER() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status,
       ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
FROM XXX
WHERE customer_manage_number = ?

我们可以从以上两个sql的执行结果中看出来PARTITION BY分组的效果。即PARTITION BY会对prodist_sku_num进行分组，然后在组内进行排序，从返回的行编码rn也可以看出来，只会在组内进行排序。

• 使用RANK() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status,
       RANK() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
FROM abs_generl.ecloud_authorization_user
WHERE customer_manage_number = ?

可以看出来，当60000005734有三条数据时，会对这三条数据进行排名，如果有相同排名的，则跳过相应的名次。

• 使用DENSE_RANK() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status,
       DENSE_RANK() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
FROM abs_generl.ecloud_authorization_user
WHERE customer_manage_number = ?

可以看出来，当60000005734有三条数据时，会对这三条数据进行排名，如果有相同排名的，则标记相同的名次，并且接下来的值不会被跳过。

需求解决

初步认识到窗口函数后，我们来写一下文章刚开始的那个需求：

首先我们对prodist_sku_num分组并按指定字段排序并为每一行生成一个编码：

SELECT prodist_sku_num, customer_manage_number,expire_day, result, status,
                ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
         FROM XXX
         WHERE customer_manage_number = #{staffNumber}

然后将上面的查询结果，我们只获取rn=1的即可

SELECT DISTINCT prodist_sku_num,customer_manage_number,expire_day, result, status
FROM (
         SELECT prodist_sku_num, customer_manage_number,expire_day, result, status,
                ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn
         FROM XXX
         WHERE customer_manage_number = #{staffNumber}
     ) subquery
WHERE rn = 1;