mybatis实现排行榜SQL代码

mql如下

SELECT  b.*, (@curRand := @curRand + 1) AS rank FROM (
		SELECT trcp.user_id as userId,count( t.id ) AS finishNum,u.HEAD_PORTRAIT AS headPortrait,u.name as userName
		FROM osi_task AS t
		LEFT JOIN osi_task_relate_assign_person AS trcp ON t.ID = trcp.task_id
		LEFT JOIN osi_user_info AS u ON trcp.user_id = u.ID
		<where>
			trcp.state = #{state,jdbcType=INTEGER}
			AND t.IS_DEL = 2 AND u.type = #{userType,jdbcType=INTEGER}
		</where>
		GROUP BY trcp.user_id
		ORDER BY finishNum DESC
		<if test="state != null">
			<if test="state == 2">
				,t.finish_time DESC
			</if>
			<if test="state == 3">
				,t.end_time DESC
			</if>
		</if>
		) as b ,(SELECT @curRand := 0) AS c
		<if test="limit != null">
			limit  #{limit,jdbcType=INTEGER}
		</if>

其中rank是排名，可以自定义条件。

查询某个人的排名：

select * from (
		SELECT b.*, (@curRand := @curRand + 1) AS rank FROM (
		SELECT trcp.user_id,count( t.id ) AS myNum
		FROM osi_task AS t
		LEFT JOIN osi_task_relate_assign_person AS trcp ON t.ID = trcp.task_id
		LEFT JOIN osi_user_info AS u ON trcp.user_id = u.ID
		WHERE trcp.state = #{state,jdbcType=INTEGER}
		AND t.IS_DEL = 2 AND u.type = 6
		GROUP BY trcp.user_id
		ORDER BY myNum DESC
		) as b ,(SELECT @curRand := 0) AS c ) as d
		<where>
		 d.user_id = #{user_id,jdbcType=VARCHAR}
		</where>