双排名

set @row_num = #{offset};

select result.*, @row_num := @row_num+ 1 AS ranking from

(

SELECT

vi.*,vt.tname,ui.nick_name

FROM

video_topic_belong vtb

LEFT JOIN

video_info vi ON vtb.video_id = vi.video_id

left join

video_type vt on vi.type_id=vt.type_id

left join

user_info  ui  on ui.user_id=vi.user_id

<where>

<if test="videoId != null and videoId.trim() != ''">

and vi.`video_id` = #{videoId}

</if>

<if test="userId != null and userId.trim() != ''">

and vi.`user_id` = #{userId}

</if>

<if test="nickName != null and nickName.trim() != ''">

and ui.nick_name like concat("%",#{nickName},"%")

</if>

and vi.create_time >= str_to_date('2018-10-15', '%Y-%m-%d')

]]>

and vi.create_time <= str_to_date('2018-11-23', '%Y-%m-%d')

]]>

AND    is_private = 1

AND    vi.statues = 1

AND    vtb.topic_id = (

SELECT

topic_id

FROM

video_topic

WHERE

tname = 'ThanksToYou'

)

</where>

<choose>

<when test="sidx != null and sidx.trim() != ''">

order by vi.${sidx} ${order}

</when>

<otherwise>

order by vi.collection_num,vi.comment_num

</otherwise>

</choose>

LIMIT #{offset}, #{limit}

) as result