准备数据:
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;
数据展示情况:
在MySQL中@与@@的区别
@x
是 用户自定义的变量 (User variables are written as @var_name)
@@x
是 global或session变量 (@@global
@@session
)
@@查看全局变量:
select @@log_error;
select @@FOREIGN_KEY_CKECK;
@设置全局变量值:
SET @t1=0, @t2=0, @t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
结果为: