首先准备演示数
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 (
'1001',
'Lily',
'85');
INSERT INTO `computer_stu`
VALUES (
'1002',
'Tom',
'91');
INSERT INTO `computer_stu`
VALUES (
'1003',
'Jim',
'87');
INSERT INTO `computer_stu`
VALUES (
'1004',
'Aric',
'77');
INSERT INTO `computer_stu`
VALUES (
'1005',
'Lucy',
'65');
INSERT INTO `computer_stu`
VALUES (
'1006',
'Andy',
'99');
INSERT INTO `computer_stu`
VALUES (
'1007',
'Ada',
'85');
INSERT INTO `computer_stu`
VALUES (
'1008',
'Jeck',
'77');
添加行号
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;
转载于:https://www.cnblogs.com/canger/p/9763304.html
相关资源：各显卡算力对照表！
