sql 留着看

最新推荐文章于 2021-06-22 10:10:07 发布

lunafeifei99

最新推荐文章于 2021-06-22 10:10:07 发布

阅读量237

点赞数 1

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/Luciferluanlan/article/details/85100105

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文深入解析了一段复杂的SQL查询语句，涵盖了用户信息、订单数据的联表查询，以及多种条件筛选，包括地理位置、购买行为、活跃度和注册方式等。通过对SQL语句的逐行分析，揭示了其背后的业务逻辑和技术细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 select user_id
      from
          (select
                u.user_id,
    			u.integral,
    			SUM(o.rough_amount) totalPrice,
    			COUNT(o.id) orderNum
            FROM
            	t_user u
            LEFT JOIN t_user_info info ON u.user_id = info.user_id
            LEFT JOIN t_order o on u.user_id = o.user_id and o.status in (5,6,51)
            LEFT JOIN t_address a on u.user_id = a.user_id and a.enable = 1
            <where>
            <if test="storeId != null and storeId != ''">and u.store_id = #{storeId}</if>
            <if test="sex != null and sex != ''">and u.sex = #{sex}</if>
            <if test="birthdayCon != null">and SUBSTR(u.birthday,6,2) in
                <foreach collection="birthdayCon" index="index" item="item" open="(" separator="," close=")">
                      #{item}
                </foreach>
            </if>
            <if test="ageMin != null and ageMin != ''">and TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) <![CDATA[>=]]> #{ageMin}</if>
            <if test="ageMax != null and ageMax != ''">and TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) <![CDATA[<=]]> #{ageMax}</if>
            <if test="cityCon != null"> and
                <foreach collection="cityCon" index="index" item="item" open="(" separator="or" close=")">
                      (info.province = #{item.province}
                      and info.city = #{item.city}
                      and info.area = #{item.area})
                </foreach>
            </if>
            <if test="receiveAddCon != null"> and
                <foreach collection="receiveAddCon" index="index" item="item" open="(" separator="or" close=")">
                      (a.province_code = #{item.province}
                      and a.city_code = #{item.city}
                      and a.area_code = #{item.area})
                </foreach>
    
            </if>
            <if test="recBuyCon != null and recBuyCon != ''">
              <choose>
               <when test="recBuyCon == 1">and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[<=]]> o.create_time</when>
               <when test="recBuyCon == 2">
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[<=]]> o.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[>]]> o.create_time
               </when>
               <when test="recBuyCon == 3">
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[<=]]> o.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[>]]> o.create_time
               </when>
               <when test="recBuyCon == 4">
                  and DATE_SUB(CURDATE(), INTERVAL 180 DAY) <![CDATA[<=]]> o.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[>]]> o.create_time
               </when>
              </choose>
            </if>
            <if test="recActiveCon != null and recActiveCon != ''">
              <choose>
               <when test="recActiveCon == 1">and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[<=]]> u.login_time</when>
               <when test="recActiveCon == 2">
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[<=]]> u.login_time
                  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[>]]> u.login_time
               </when>
               <when test="recActiveCon == 3">
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[<=]]> u.login_time
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[>]]> u.login_time
               </when>
               <when test="recActiveCon == 4">
                  and DATE_SUB(CURDATE(), INTERVAL 180 DAY) <![CDATA[<=]]> u.login_time
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[>]]> u.login_time
               </when>
              </choose>
            </if>
            <if test="regTimeCon != null and regTimeCon != ''">
              <choose>
               <when test="regTimeCon == 1">and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[<=]]> u.create_time</when>
               <when test="regTimeCon == 2">
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[<=]]> u.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[>]]> u.create_time
               </when>
               <when test="regTimeCon == 3">
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[<=]]> u.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[>]]> u.create_time
               </when>
               <when test="regTimeCon == 4">
                  and DATE_SUB(CURDATE(), INTERVAL 180 DAY) <![CDATA[<=]]> u.create_time
                  and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <![CDATA[>]]> u.create_time
               </when>
              </choose>
            </if>
            <if test="regWayCon != null and regWayCon != ''">
                <if test="regWayCon == 1">and u.origin = 8</if>
                <if test="regWayCon == 2">and u.origin in (0,1,2,3,4,5)</if>
            </if>
            </where>
            GROUP BY u.user_id) a
        <where>
          <if test="buyTrade != null"> or orderNum <![CDATA[>=]]> #{buyTrade}</if>
          <if test="buyMoney != null"> or totalPrice <![CDATA[>=]]> #{buyMoney}</if>
          <if test="buyIntegral != null"> or integral <![CDATA[>=]]> #{buyIntegral}</if>
        </where>