Mysql 排名，分组组内排名

测试用的资源
数据库数据
查询语句

一、排名

1. 普通排名:从1开始，顺序下排

	SELECT a.*, 
		( @rowNum := @rowNum + 1 ) AS rank 
	FROM student_scores a,
		( SELECT @rowNum := 0 ) b 
	ORDER BY
		a.scores DESC

2. 并列排名：相同的值是相同的排名

	SELECT
		a.*,
		@pRank :=IF( @pre = a.scores, @pRank, @rowNum ) AS rank,
		@pre := a.scores,
		@rowNum := @rowNum + 1 
	FROM
		student_scores a,
		( SELECT @pRank := 0, @rowNum := 1, @pre := 0 ) b 
	ORDER BY
		a.scores DESC

3. 并列排名：相同的值是相同的排名,但排序是一直递增的，对比上

	SELECT
		a.*,
		@pRank :=IF( @pre = a.scores, @pRank, @rowNum ) AS rank,
		@pre := a.scores,
		@rowNum := @rowNum + 1 
	FROM
		student_scores a,
		( SELECT @pRank := 0, @rowNum := 1, @pre := 0 ) b 
	ORDER BY
		a.scores DESC

4. 分组后并列排名：组内相同数值排名相同

	SELECT a.*,
		IF(@gradePre=a.grade,
				CASE 
				   WHEN @pre = a.scores THEN  @rowNum 
				   WHEN @pre := a.scores THEN @rowNum := @rowNum + 1
	      END,
	      @rowNum:=1) AS rank,
	@gradePre:=a.grade,
	@pre:=a.scores
	FROM student_scores a,( SELECT @gradePre := NULL, @rowNum := 0, @pre :=NULL ) b 
	ORDER BY
		a.grade,
		a.scores DESC

5. 排序
6. 排序