mysql 添加行号

首先准备演示数

DROP TABLE IF EXISTS `computer_stu`;

CREATE TABLE `computer_stu` (

  `id` int(11) NOT NULL,

  `name` varchar(20) DEFAULT NULL,

  `score` float DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of computer_stu

-- ----------------------------

INSERT INTO `computer_stu` VALUES ('', 'Lily', '');

INSERT INTO `computer_stu` VALUES ('', 'Tom', '');

INSERT INTO `computer_stu` VALUES ('', 'Jim', '');

INSERT INTO `computer_stu` VALUES ('', 'Aric', '');

INSERT INTO `computer_stu` VALUES ('', 'Lucy', '');

INSERT INTO `computer_stu` VALUES ('', 'Andy', '');

INSERT INTO `computer_stu` VALUES ('', 'Ada', '');

INSERT INTO `computer_stu` VALUES ('', 'Jeck', '');

添加行号

SELECT

    (@rownumber :=@rownumber + 1) AS line,

    id,

    NAME,

    score

FROM

    (SELECT * FROM computer_stu) c,

    (SELECT @rownumber := 0) r;

结果如下

分组添加行号

SELECT

    @rownumber:=

    CASE

        WHEN @preScore = c.score THEN (@rownumber + 1)

        ELSE 1

    END  AS line,

    @preScore := c.score

    NAME,

    score

FROM

    (SELECT * FROM computer_stu ORDER BY computer_stu.score) c,

    (SELECT @rownumber := 0,@preScore:='') r;