创建表
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;