用户表sql语句

一个处女座的暖男程序猿

于 2024-09-20 16:41:03 发布

阅读量183

点赞数 3

文章标签： sql 数据库前端

本文链接：https://blog.csdn.net/weixin_46597615/article/details/142389485

版权

  SELECT
        su.user_id AS userId,
        su.user_name AS userName,
        su.nick_name AS nickName,
        su.email AS email,
        su.phonenumber AS phonenumber,
        su.unit AS unit,
        su.education AS education,
        su.speciality AS speciality,
        su.open_id AS openId,
        su.is_subscribe AS isSubscribe,
        GROUP_CONCAT(DISTINCT sd.demand_content SEPARATOR ', ') AS demandName,
        GROUP_CONCAT(DISTINCT sp.post_name SEPARATOR ', ') AS postName,
        su.is_vip AS isVip,
        rc.name AS regChannelName,
        suo.expiration_time AS expirationTime,
        su.login_date AS loginDate,
        su.create_time AS createTime,
        su.is_bind AS isBind,
        su.platform AS platform,
        su.other_source AS otherSource,
        su.other_know_channel AS otherKnowChannel,
        su.know_channel AS knowChannel,
        su.sex AS sex,
        su.post AS post,
        su.demand AS demand,
        su.login_ip AS loginIp,
        su.has_complete AS hasComplete,
        MAX(ul.created_at) AS createdAt,
        SUM(ul.coins) AS totalCoins,
        COUNT(CASE WHEN ul.action = 'LOGIN' THEN 1 END) AS loginCount,
        COUNT(CASE WHEN ul.action = 'LOGIN' AND DATE_FORMAT(ul.created_at, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m') THEN 1 END) AS monthlyLoginCount,
        CASE WHEN COUNT(CASE WHEN co.status = 1 THEN 1 END) > 0 THEN 1 ELSE 0 END AS hasPaid  -- 是否支付
        FROM
        sys_user su
        LEFT JOIN sys_user_order suo ON su.user_id = suo.user_id
        LEFT JOIN sys_user_demand sud ON su.user_id = sud.user_id
        LEFT JOIN sys_demand sd ON sud.demand_id = sd.demand_id
        LEFT JOIN sys_user_post sup ON su.user_id = sup.user_id
        LEFT JOIN sys_post sp ON sup.post_id = sp.post_id
        LEFT JOIN registration_channels rc ON su.source = rc.id
        LEFT JOIN user_coin_log ul ON ul.user_id = su.user_id
        LEFT JOIN chart_orders co ON su.user_id = co.user_id  -- 左连接订单表
        WHERE
        1=1
        <if test="source != null">
            AND su.source = #{source}
        </if>
        <if test="knowChannel != null">
            AND su.know_channel = #{knowChannel}
        </if>
        <if test="phoneNumber != null and phoneNumber!=''">
            AND su.phonenumber = #{phoneNumber}
        </if>
        <if test="startTime != null and startTime != ''">
            <![CDATA[ and date_format(su.create_time,'%y%m%d') >= date_format(#{startTime},'%y%m%d') ]]>
        </if>
        <if test="endTime != null and endTime != ''">
            <![CDATA[ and date_format(su.create_time,'%y%m%d') <= date_format(#{endTime},'%y%m%d') ]]>
        </if>
        GROUP BY
        su.user_id, su.user_name, su.nick_name, su.email, su.phonenumber, su.sex, su.unit, su.education,
        su.speciality, su.open_id, su.is_subscribe, su.is_vip, rc.name, suo.expiration_time, su.login_date,
        su.create_time
        ORDER BY
        su.create_time DESC