MYSQL通过子表筛选主表

记录一个sql

先说前提：主表是用户表，子表是用户购买记录表。用户可能一条购买记录都没有，也可以有一条或多条购买记录。

再说需求：通过是否有购买记录对用户进行筛选。

SELECT
    uio.user_id,
    uio.user_name,
    uio.real_name,
    uio.phone,
    uio.email,
    uio.add_time,
    case when platform_num is null then 0 else 1 end  is_experience,
    uio.is_forbidden
FROM
    user_info uio
LEFT JOIN
    (select user_id, count(platform_id) platform_num #platform_num 为 null 或 大于0的数字
        from user_platform
        where end_time > now()
        group by user_id
    ) ucy ON uio.user_id = ucy.user_id

<if test="isExperience != null and isExperience != ''">
    having is_experience = #{isExperience}
</if>