计算同一门课程下所有人的分数排名。
oracle
建表语句
create table a(
id number ,
name varchar2(250),
user_name varchar2(250),
score number
);
查询语句:
SELECT RANK() OVER(PARTITION BY NAME ORDER BY SCORE DESC) AS PAIMING,
NAME,
USER_NAME,
SCORE
FROM (SELECT * FROM A ORDER BY NAME, SCORE DESC) A
结果:
mysql
建表语句
CREATE TABLE a (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
major VARCHAR(20),
user_name VARCHAR(20),
score INT
)
查询语句
此处思路:定义一个变量@rank用来计算排名,@nowType存当前数据的课程类型,@lastType存放上一条记录的课程类型,@rank递增,如果当前课程类型和上一条的课程类型不是 一种的时候,重置@rank的值为1,重新进行排序。
SELECT a.*,
@lastType := @nowType AS last_type,
@nowType := a.major AS now_type,
IF(@nowtype = @lastType, @rank := @rank + 1, @rank := 1) AS rank
FROM a AS a, (SELECT @rank := 0, @nowType := 0) AS b
ORDER BY major,score DESC
结果:
---------- ^ - ^-----------我要做那颗最张扬的韭菜!