<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.dromara.zjk.mapper.ZjkCreditUserMapper">
    <select id="getExpertPointRank" resultType="org.dromara.zjk.domain.vo.CreditExpertGetVO">
        SELECT aa.*, ze.expert_name, RANK() OVER (ORDER BY creditCount DESC) AS rank
        from (select expert_id, sum(credit) creditCount
              from zjk_credit
              where CREATE_TIME > #{rangeDay}
              group by expert_id) aa
                 inner JOIN zjk_expert ze on aa.expert_id = ze.expert_id
        order by aa.creditCount desc
            limit 10;
    </select>

    <select id="getRankAndPoint" resultType="org.dromara.zjk.domain.vo.CreditShowVO">
        SELECT aa.*
        FROM (SELECT zpu.user_id,
                     zpu.expert_id,
                     RANK() OVER ( ORDER BY credit_count DESC ) AS RANK, zpu.credit_count AS creditCount
              FROM zjk_credit_user zpu) aa
        WHERE aa.user_id = #{userId}
    </select>

    <update id="removeTheBlacklist">
        UPDATE zjk_credit_user
        SET reward_id = null,
        reward_title = null
        WHERE user_id = #{userId}
        AND expert_id = #{expertId}
    </update>

</mapper>
