mysql 数据库排名实现

声明三个初始变量

@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0

将@rownum +1 ，赋值给 @rownum

@rownum := @rownum + 1 

@rownum := @rownum + 1 AS num_tmp
这一行在下面内容之前，则能实现并列排序。只写这一行的话，输出的排序是顺序排序

@incrnum := CASE
	WHEN @rowtotal = obj.borrowedNum THEN
			@incrnum
	WHEN @rowtotal := obj.borrowedNum THEN
			@rownum
	END AS rownum

SELECT
	obj.STU_NAME,
	obj.CONTACT_INFO,
	obj.borrowedNum,
  @rownum := @rownum + 1 AS num_tmp,
			@incrnum := CASE
	WHEN @rowtotal = obj.borrowedNum THEN
			@incrnum
	WHEN @rowtotal := obj.borrowedNum THEN
			@rownum
	END AS rownum
FROM
	(
		SELECT
			student.STU_NAME,
			student.CONTACT_INFO,
			a.s_c_id borrowedNum,
			@rownum := 0 ,@rowtotal := NULL ,@incrnum := 0
		FROM
			student_class a
		LEFT JOIN student ON a.STU_ID = student.STUDENT_ID
		WHERE
			a.CM_ID IN (129381)
		AND a.SC_STATE IN (119, 120)
		GROUP BY
			a.STU_ID HAVING borrowedNum > 0
		ORDER BY
			borrowedNum DESC
	) AS obj