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