查询sql_qq 查询 sql-CSDN博客

本文链接：https://blog.csdn.net/qq_42520477/article/details/99485231

<resultMap id="UserEarningsRecordVo" type="com.sunsoft.facade.admin.vo.UserEarningsRecordVo"
   extends="BaseResultMap">
   <result column="user_code" property="userCode" jdbcType="VARCHAR" />
   <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
   <result column="user_img" property="userImg" jdbcType="VARCHAR" />
   <result column="user_phone" property="userPhone" jdbcType="VARCHAR" />
<result column="user_type" property="userType" jdbcType="INTEGER" />
<result column="month_salesAll" property="monthSalesAll" jdbcType="DECIMAL" />
<result column="total_salesAll" property="totalSalesAll" jdbcType="DECIMAL" />
<result column="month_give_salesAll" property="monthGiveSalesAll" jdbcType="DECIMAL" />
</resultMap>

   <select id="selectEarningsByParam" resultMap="UserEarningsRecordVo"
       parameterType="java.lang.String">
       select
       DISTINCT e.user_id,
       u.user_code ,
       u.user_phone ,
       u.user_type ,
       IFNULL(u.nick_name,'0') as nick_name,
       IFNULL(u.user_img,'0') as user_img ,
       (
       SELECT
       COUNT(*)
       FROM
       vip_user_fans AS f
       WHERE
       f.user_id = e.user_id
       ) AS fans,
       (
       SELECT
       SUM(total_sales)
       FROM
       user_earnings_record
       WHERE
       user_id = u.id
       ) AS total_salesAll,
       (
       SELECT
       SUM(month_sales)
       FROM
       user_earnings_record
       WHERE
       user_id = u.id
       ) AS month_salesAll,

       (
       SELECT
       SUM(month_give_sales)
       FROM
       user_earnings_record
       WHERE
       user_id = u.id
       ) AS month_give_salesAll
       from user_earnings_record as e
       LEFT JOIN vip_user_info AS u ON e.user_id = u.id
       where e.user_id = u.id
       <if test="userCode !=null and ''!=userCode">
           AND u.user_code LIKE CONCAT('%',trim(#{userCode}),'%')
       </if>
       <if test="userPhone !=null and ''!=userPhone">
           AND u.user_phone LIKE CONCAT('%',trim(#{userPhone}),'%')
       </if>
   </select>

<select id="selectLookEarningsByuserId" resultMap="UserEarningsRecordVo"
       parameterType="java.lang.String">
       select
       e.user_id,
       e. `year`,
       e.`month`,
       e.month_sales,
       e.month_give_sales,
e.total_sales,
       e.Invite_number,
       e.give_status,
       u.user_code ,
       u.nick_name ,
       u.user_phone
       from user_earnings_record as e
       LEFT JOIN vip_user_info AS u ON e.user_id = u.id
       where user_id = #{userId,jdbcType=VARCHAR}
       ORDER BY e.`year` DESC,
    e.`month` DESC
   </select>

<select id="selectApplyEarningsByuserId" resultMap="UserEarningsRecordVo"
       parameterType="java.lang.String">
       select
       e.id,
       e.apply_time,
       e.month_sales,
       e.month_give_sales,
e.total_sales,
       e.give_status,
       u.user_code ,
       u.nick_name ,
       u.user_phone
       from user_earnings_record as e
       LEFT JOIN vip_user_info AS u ON e.user_id = u.id
       where give_status in(2,3)
       ORDER BY e.apply_time DESC
   </select>