<?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="com.cyksj.mapper.UserMapper">


    <select id="selectUnBindUserNum" resultType="java.lang.Integer">
        select count(*)
        from user u
                 left join (select id, user_id
                            from `user_bind_detail`
                            UNION all
                            select id, email_user_id
                            from `user_bind_detail` uni
                            UNION all
                            select id, phone_user_id
                            from `user_bind_detail`) s on s.user_id = u.id
        where s.id is null
    </select>

    <select id="getLargeNewConsumer" resultType="java.lang.Integer">
        select count(s.user_id)
        from (select user_id
              from order_don o
              where o.relation_id != 0 and o.status not in ('close','noPayment','refund') and created_time >= #{beginOfDay}
              group by user_id
              having sum(money) > #{largeLimit}) s
        where not exists (select user_id,sum(money) from order_don o where o.user_id = s.user_id and o.relation_id != 0 and o.status not in ('close','noPayment','refund') and o.created_time &lt; #{beginOfDay} group by user_id, date_format(o.created_time,'%Y-%m-%d')
        having sum(money) >  #{largeLimit})
    </select>

    <select id="getNewMaliceUser" resultType="java.lang.Integer">
        select count(distinct o.user_id)
        from (select user_id
              from order_don
              where created_time >= #{beginOfDay}
                and status not in ('close', 'noPayment')
                and buyer_id is not null
              group by user_id
              having count(distinct buyer_id) > 4) o
                 inner join user u on u.id = o.user_id
    </select>
</mapper>