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
