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

创建表

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;