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;