欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql数据库的多字段排名实例讲解

程序员文章站 2022-06-20 23:00:33
创建表 create database `test_db`; use `test_db`; drop table if exists `rank`; create table `ran...

创建表

create database `test_db`;

use `test_db`;
drop table if exists `rank`;
create table `rank` (
  `id` int(11) not null auto_increment,
  `rank_id` int(11) default null,
  `c1` varchar(20) default null,
  `c2` varchar(20) default null,
  primary key (`id`)
) engine=innodb auto_increment=18 default charset=utf8;

/*data for the table `rank` */
insert  into `rank`(`id`,`rank_id`,`c1`,`c2`) values (1,17,'n','n'),(2,18,'n','n'),(3,18,'n','y'),(4,19,'n','n'),(5,19,'n','n'),(6,19,'n','y'),(7,20,'n','n'),(8,20,'n','y'),(9,20,'n','y'),(10,21,'n','n'),(11,22,'n','n'),(12,23,'n','n'),(13,24,'n','n'),(14,25,'n','n'),(15,25,'n','n'),(16,25,'n','n'),(17,26,'n','n');


实现的效果:3列字段中不同一列不同累加排名一次,相同情况不累加排名
查询脚本

select 
  obj.rank_id,
  obj.c1,
  obj.c2,
     case   
       when @col0 = obj.rank_id and @col1 = obj.c1 and @col2 = obj.c2 then
        @rownum := @rownum  
       when @col0 = obj.rank_id and @col1 <> obj.c1 and @col2 = obj.c2 then
        @rownum := @rownum + 1
       when @col0 = obj.rank_id and @col1 = obj.c1 and @col2 <> obj.c2 then
        @rownum := @rownum + 1
       else
        @rownum := @rownum + 1
       end rownum,@col0:=obj.rank_id,@col1:=obj.c1,@col2:=obj.c2
from 
    (
        select 
            rank_id,
            c1,
            c2  
        from rank
        order by rank_id ,c2 desc
    ) as obj,
    (select @rownum := 0, @rowtotal := null) r;