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关联
*/
DROP TABLE IF EXISTS report_agent_list;

CREATE TABLE report_agent_list AS
/* 代理列表视图 */

DROP VIEW IF EXISTS manage_agent_list;

CREATE OR REPLACE VIEW manage_agent_list AS
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 = '3'
        GROUP BY
            w.owner_id
    )
SELECT
    mu.nickname as agent_nickname /* 代理昵称 */,
    ai.created_at as apply_time /* 申请时间 */,
    mu.gender as gender /* 性别 */,
    mu.avatar as avatar /* 头像 */,
    mu.mobile as mobile /* 联系电话 */,
    ai.area_code as area /* 代理地区 */,
    COALESCE(w.total_income, 0) as total_income /* 总收益金额 */,
    COALESCE(aw.total_withdraw_amount, 0) as withdraw_amount /* 提现金额 */,
    COALESCE(w.available_balance, 0) as balance /* 余额 */
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 = '3'
    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;

select *
from
    agent_infos as ag
    inner join member_users as mu on ag.user_id = mu.id;