owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/07代理列表.sql

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
/*
    您是一位专业的dba，同时会业务分析。请参考以上数据库的设计文档，给我写一个sql，关联查询出：代理昵称、申请时间、性别、头像、联系电话、代理地区、总收益金额、体现金额、余额
请按照的我的要求生成查询sql语句：
1. 请从如下 agent_infos   member_users   wallet  wallet_withdraw_records等一些表查找
2. agent_infos通过user_id关联的member_users表
3. wallet 和 member_users通过判断owner_type和owner_id关联
4. wallet_withdraw_records和wallet通过wallet_id关联
*/
/* 代理信息统计查询 */
WITH agent_withdraw AS (
    SELECT 
        w.owner_id,
        COALESCE(SUM(wr.amount), 0) AS total_withdraw_amount /* 总提现金额 */
    FROM wallet w
    LEFT JOIN wallet_withdraw_records wr ON w.id = wr.wallet_id 
        AND wr.audit_state = 'approved' 
        AND wr.state = 'success'
    WHERE w.owner_type = 'AGENT'
    GROUP BY w.owner_id
)
SELECT 
    mu.nickname /* 代理昵称 */,
    ai.created_at /* 申请时间 */,
    mu.gender /* 性别 */,
    mu.avatar /* 头像 */,
    mu.mobile /* 联系电话 */,
    ai.area_code /* 代理地区 */,
    COALESCE(w.total_income, 0) /* 总收益金额 */,
    COALESCE(aw.total_withdraw_amount, 0) /* 提现金额 */,
    COALESCE(w.available_balance, 0) /* 余额 */
FROM agent_infos ai
INNER JOIN member_users mu ON ai.user_id = mu.id
LEFT JOIN wallet w ON w.owner_id = ai.id 
    AND w.owner_type = 'AGENT'
LEFT JOIN agent_withdraw aw ON aw.owner_id = mu.id
WHERE ai.deleted_at IS NULL
    AND mu.deleted_at IS NULL
    AND w.deleted_at IS NULL;