Mysql排序案例
1、准备数据表和数据
CREATE TABLE `exam`
(
`examid`
int(11) NOT NULL AUTO_INCREMENT
,
`studentId`
varchar(20) NOT NULL
,
`subjectId`
varchar(20) NOT NULL
,
`score`
int(11) NOT NULL
,
PRIMARY KEY
(`examid`
)
) ENGINE
=InnoDB AUTO_INCREMENT
=8 DEFAULT CHARSET
=utf8
-- 因为主键是自增的所以不需要我们进行维护
INSERT INTO exam VALUES
(NULL
,'123456',1,60),(NULL
,'126556',1,75),(NULL
,'12156',2,93),(NULL
,'654054',1,86),(NULL
,'652314',2,80),(NULL
,'645874',2,95),(NULL
,'654321',2,93)
通过查询我们可以看到数据表是这样的
SELECT
* FROM exam
;
2、要求查询出，科目编号为 2 的科目成绩及排名（普通查询即可）
-- 查询一个
@rownum增加
1，通过
:=赋值给新的
@rownum
SELECT examid
,studentid
,subjectId
,score
,@rownum:=@rownum+1 AS rank
-- 定义变量初始化
FROM exam a
,(SELECT
@rownum:=0) b
-- 查询科目编号为
2
WHERE SubjectId
=2
-- 对成绩进行排序
ORDER BY a
.score DESC
;
注意：代码中“(select @rownum:=0) b”的作用是给变量 rownum 赋值为 0，b一定要带上，不带就报错。 运行效果如下： 观察上图可以发现一个问题，就是第二名和第三名的分数一样，但是排名不同 下面我们就来解决一下分数相同排名也要一样的问题
3、分数相同，名次也要相同
SELECT examid
,studentid
,subjectId
,score
,
-- 使用
case进行比较
CASE
-- 当新查询的一条数据的分数和上一条的分数相同时，输出相同名次
WHEN
@PREV = a
.score THEN
@rownum
-- 当分数不同是执行下面sql，名次增加一
WHEN
@PREV := a
.score THEN
@rownum := @rownum+1
END AS rank
FROM exam a
,(SELECT
@rownum:=0,@PREV:=NULL
) b
WHERE SubjectId
=2
ORDER BY a
.score DESC
;
说明：@PREV := a.score既能返回true，又能将a.score赋值与@PREV.
效果图如下： 上面的排名是没有问题的，但是也有一些情况下，要求当出现并列名次时，后面的同学进行跳跃排名
3、实现跳跃排序
SELECT EXAMID
,STUDENTID
,SUBJECTID
,SCORE
,RANK FROM
(SELECT EXAMID
,STUDENTID
,SUBJECTID
,SCORE
,
-- if相当于java中的三元运算符，当
@PREV=A
.SCORE输出
@ROWNUM,不相等时输出
@INC
@ROWNUM:=IF(@PREV=A
.SCORE
,@ROWNUM,@INC) AS RANK
,
-- 没执行一次
,变量增加
1
@INC:=@INC+1,
-- 将新的分数赋值于变量
@PREV
@PREV:=A
.SCORE
-- 初始化三个参数
FROM EXAM A
,(SELECT
@ROWNUM:=0,@PREV:=NULL
,@INC:=1) B
-- 查询科目为
2的
WHERE SUBJECTID
=2
-- 排序
ORDER BY A
.SCORE DESC
) TB
;
效果图如下： 上述三种排序方法在项目中都可以使用，根据自己的项目需求自行选择即可。
