mysql查询近期数据并排序

mysql查询最近7天(过去七天完整,不包括今天)的总贡献值并进行排序，输出序号

<select id="findTotalContributionByDate" resultType="com.ruoyi.system.dto.ContributionDTO">
        SELECT a.*,
               (@i := @i + 1) AS `index`
        FROM (
                     SELECT SUM(a.contri_value) AS `total`,
                            a.user_id as userId
                     FROM lawyer_contri_record a
                     WHERE create_time >= DATE(NOW()) + INTERVAL - #{date,jdbcType=INTEGER} DAY
                       AND create_time <![CDATA[<]]> DATE(NOW()) + INTERVAL 0 DAY
                     GROUP BY user_id
                     ORDER BY total DESC
                     ) AS a,
             (SELECT @i := 0) AS b
    </select>

参考