创建一个学生表,插入10条数据
CREATE TABLE student (id int, name varchar(20), score integer); INSERT INTO student VALUES (1, 'jack', 90); INSERT INTO student VALUES (2, 'tom', 85); INSERT INTO student VALUES (3, 'allen', 79); INSERT INTO student VALUES (4, 'michael', 95); INSERT INTO student VALUES (5, 'steven', 65); INSERT INTO student VALUES (6, 'kevin', 100); INSERT INTO student VALUES (7, 'kate', 60); INSERT INTO student VALUES (8, 'david', 55); INSERT INTO student VALUES (9, 'kelly', 95); INSERT INTO student VALUES (10, 'bale', 99);
查询分数小于80分的学生,显示row_number
SELECT @curRow := @curRow + 1 AS row_num, s.id, s.name, s.score FROM student s JOIN (SELECT @curRow := 0) r WHERE s.score < 80;