MySQL中实现Rank排名高级函数

 先举例一个

SELECT  name, time, @curRank := @curRank + 1 AS rank
FROM 表名, (
SELECT @curRank := 0
) q
ORDER BY time

 

1.要在mysql中声明一个变量，你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化，而不需要单独的SET命令。当然，也可以使用SET，但它会处理两个查询

2.当使用RANK()函数时，如果两个或以上的行排名并列，则相同的行都会有相同的排名，但是实际排名中存在有关系的差距。

	SELECT
		@rownum := @rownum + 1 AS rank,
		a.*
		FROM
		(
		SELECT
		p.project_id projectId,
		IFNULL(p.project_name,
		'') projectName,
		IFNULL(a1.url, '') projectPicUrl,
		IFNULL(p.donation_area, '') donationArea,
		COUNT(s.USER_ID) loveValue,
		p.publish_time publishTime,
		p.pro_commit_date proCommitDate
		FROM
		gongyi_project_support s
		LEFT JOIN projects p ON s.PROJECT_ID =
		p.project_id
		LEFT JOIN
		project_details d ON s.PROJECT_ID = d.project_id
		LEFT JOIN attachments
		a1 ON SUBSTRING_INDEX(d.project_pic, ',', 1) =
		a1.attach_id
		WHERE
		1 = 1
		<if test="dateType ==1">
			AND YEARWEEK(date_format(s.CREATE_DATE,'%Y-%m-%d')) =
			YEARWEEK(now())
		</if>
		<if test="dateType ==2">
			AND DATE_FORMAT(s.CREATE_DATE, '%Y%m') =
			DATE_FORMAT(CURDATE( ), '%Y%m')
		</if>
		<if test="dateType ==3">
			AND YEAR(s.CREATE_DATE)=YEAR(NOW())
		</if>
		AND p.project_on_off = 5
		AND
		p.project_status IN (11, 12, 13)
		GROUP BY
		s.PROJECT_ID
		ORDER BY
		loveValue
		DESC
		) a,
		(SELECT @rownum := 0) b