mysql实现分组排名

mysql实现分组排名

在oracle中，可以使用row_number() over(partition by ... order by ...)的方法实现分组排名，在mysql中并不提供类似方法，但也可以实现这样的功能

实例演示：

实例1：实现分组排名-单字段

# mysql实现分组排名-单字段
select
  t.member_id,# 分组字段
    t.order_date,# 排名字段

    # 排名计算逻辑
    case
      when @num = t.member_id then @rank:=@rank+1 
        else @rank:=1
    end rownum,

    # 分组字段临时参数的设置一定要放到排名计算逻辑后面
    @num:=t.member_id

from 
  # 这个r表是必须要的,先做变量初始化，否则第一次查询的时候会有问题
  (select @rank:=0,@num:=0 from dual) r,
  bi_t_pro_z_achievement_detail t
order by 
  t.member_id,# 分组字段
  t.order_date # 排名字段

上述实例中，我的主表是bi_t_pro_z_achievement_detail，实现了根据member_id进行分组，然后根据order_date从小到大排名

实例2：实现分组排名-多字段

# mysql实现分组排名-多字段
select 
  t.store_id,# 分组字段1
    t.member_id,# 分组字段2
    t.order_date,# 排名字段1

    # 排名计算逻辑
    case 
      when @num1 = t.store_id and @num2 = t.member_id  then @rank:= @rank+1 
        else @rank:=1
    end rank,

    # 分组字段临时参数的设置一定要放到排名计算逻辑后面
    @num1:=t.store_id as v_num1,# 分组字段临时参数1
    @num2:=t.member_id as v_num2# 分组字段临时参数2

from 
  # 这个r表是必须要的,先做变量初始化，否则第一次查询的时候会有问题
  (select @num1:=0,@num2:=0,@rank:=0 from dual) r,
  bi_t_pro_z_achievement_detail t # 主表
order by 
    t.store_id,# 分组字段
    t.member_id, # 分组字段
    t.order_date desc # 排名字段 

上述实例中，我的主表是bi_t_pro_z_achievement_detail，实现了根据store_id和member_id进行分组，然后根据order_date从大到小排名

实例3：实现分组排名-多字段

# mysql实现分组排名-多字段：排名相等则同名
select 
    t.store_id,# 分组字段1
    t.member_id,# 分组字段2
    t.commodity_cost,# 排名字段1

    # 排名计算逻辑
    case 
      when @num1 = t.store_id and @num2 = t.member_id and @num3 = t.commodity_cost then @rank:=@rank+0
      when @num1 = t.store_id and @num2 = t.member_id  then @rank:= @rank+1 
        else @rank:=1
    end rank,

    # 分组字段临时参数的设置一定要放到排名计算逻辑后面
    @num1:=t.store_id as v_num1,# 分组字段临时参数1
    @num2:=t.member_id as v_num2,# 分组字段临时参数2
    @num3:=t.commodity_cost as v_num3 # 排名字段临时参数1：这个参数主要用来判断同名的情况

from 
  # 这个r表是必须要的,先做变量初始化，否则第一次查询的时候会有问题
  (select @num1:=0,@num2:=0,@num3:=0,@rank:=0 from dual) r,
  bi_t_pro_z_achievement_detail t # 主表
order by 
  t.store_id,# 分组字段
  t.member_id, # 分组字段
  t.commodity_cost desc # 排名字段 

上述实例中，我的主表是bi_t_pro_z_achievement_detail，需求是需要根据store_id和member_id进行分组，然后根据commodity_cost从大到小排名，但是这里会有个问题，因为同一个store_id不同member_id的commodity_cost有可能会一样，就像我们平时考试，同一个班的不同人，可能两个人的总分是一样的高的，这时候两个人应该是并列的名次，而不是一前一后，所以这个方法实现了类似oracle的rank() over(partition by ... order by ...)的功能

实例4：实现分组排名-多字段且分组字段是字符串

上述实例中，我的分组字段store_id和member_id都是 int 类型，不会有问题

但是如果我们的分组字段中有字符串类型，会有一个坑，因为我们初始化 @num1 和 @num2 的时候，默认给的值是 0 ，这两个变量会初始化为数字类型，判等的时候会产生数据丢失，解决的方式是初始化为varchar类型

示例如下,假设 store_name 是 varchar（16）：

# mysql实现分组排名-多字段：排名相等则同名
select 
    t.store_name,# 分组字段1
    t.member_id,# 分组字段2
    t.commodity_cost,# 排名字段1

    # 排名计算逻辑
    case 
      when @num1 = t.store_nameand @num2 = t.member_id and @num3 = t.commodity_cost then @rank:=@rank+0
      when @num1 = t.store_id and @num2 = t.member_id  then @rank:= @rank+1 
        else @rank:=1
    end rank,

    # 分组字段临时参数的设置一定要放到排名计算逻辑后面
    @num1:=t.store_id as v_num1,# 分组字段临时参数1
    @num2:=t.member_id as v_num2,# 分组字段临时参数2
    @num3:=t.commodity_cost as v_num3 # 排名字段临时参数1：这个参数主要用来判断同名的情况

from 
  # 这个r表是必须要的,先做变量初始化，否则第一次查询的时候会有问题
  (select @num1:='xxxxxxxxxxxxxxx',@num2:=0,@num3:=0,@rank:=0 from dual) r,
  bi_t_pro_z_achievement_detail t # 主表
order by 
  t.store_id,# 分组字段
  t.member_id, # 分组字段
  t.commodity_cost desc # 排名字段 

上面例子中，@num1:='xxxxxxxxxxxxxxx' 表示初始化为 varchar 类型，里面的字符串也可以不用 x ，只要确保是不存在的值且长度大于 store_name 的长度 varchar（16） 即可