Mysql实现多条件排名

SELECT
	s.cuserid id,
	c1.rank,
	s.sum,
	c1. NAME,
	f1.faculty,
	p1.position,
	c1.wantposition,
	c1.recommendtype,
@curRank :=
		IF (
			@prevRank = s.sum and @userrank=c1.rank,
			@curRank,
			@incRank
		) AS rank5,
		@incRank := @incRank + 1,
		@prevRank := s.sum,
		@userrank := c1.rank
FROM
	(
		SELECT
			candidate.cuserid,
			candidate.SUM,
			cuser.rank
		
	FROM
		candidate
	LEFT JOIN cuser ON (candidate.cuserid = cuser.id)
WHERE
	candidate.voteid = 134
ORDER BY candidate.sum desc,cuser.rank asc
	) s
LEFT JOIN cuser c1 ON (s.cuserid = c1.id)
LEFT JOIN faculty f1 ON c1.faculty = f1.id
LEFT JOIN position p1 ON c1.position = p1.id,
	(
		SELECT
			@curRank := 0,
			@prevRank := NULL,
			@userrank := NULL,
			@incRank := 1
	) r
WHERE
	s.cuserid IS NOT NULL;

运行结果：

其中重要的知识点有两个：

一：

@curRank :=
		IF (
			@prevRank = s.sum and @userrank=c1.rank,
			@curRank,
			@incRank
		) AS rank5,
		@incRank := @incRank + 1,
		@prevRank := s.sum,
		@userrank := c1.rank

判断 sum是否相同，如果相同，则排名不加1，如果不想同，则加1.下面是对做排序的一些变量的赋值代码：

	(
		SELECT
			@curRank := 0,
			@prevRank := NULL,
			@userrank := NULL,
			@incRank := 1
	) r

二：

ORDER BY candidate.sum desc,cuser.rank asc

这里是对candidate.sum进行降序排列
对 所在candidate.sum 中的cuser.rank进行升序排列：