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

-- Active: xiaoding_test@@192.168.110.85@3306
/*

这段sql语句是根据"market_dist_teams"表获取的，这个表的解释是：
1、当一个用户A邀请另一个用户B的时候，数据是这样的
-  object_id是A的id
- object_type是A的用户类型
-  user_id是B的id
- 统计的时候A是B的一级邀请人
2、当用户B邀请C的时候，数据是这样的
-  object_id是B的id
- object_type是B的用户类型
-  user_id是C的id
- 统计的时候B是C的一级邀请人
- 统计的时候A是C的二级邀请人

请重写我的统计sql
*/

WITH 
/* 一级邀请人信息 */
inviter_level1 AS (
    SELECT 
        mdt1.user_id,
        m1.nickname
    FROM market_dist_teams mdt1
    LEFT JOIN member_users m1 ON mdt1.object_id = m1.id 
    WHERE mdt1.level = '1'
),
/* 二级邀请人信息 */
inviter_level2 AS (
    SELECT 
        mdt1.user_id,
        m2.nickname
    FROM market_dist_teams mdt1
    LEFT JOIN market_dist_teams mdt2 ON mdt1.object_id = mdt2.user_id
    LEFT JOIN member_users m2 ON mdt2.object_id = m2.id
    WHERE mdt1.level = '1'
    AND mdt2.level = '1'
),
/* 钱包余额信息 */
coach_wallet AS (
    SELECT 
        owner_id,
        total_balance
    FROM wallet 
    WHERE owner_type = 'COACH'
)
SELECT
    /* 性别 */
    mu.gender,
    /* 技师名称 */
    ci.nickname,
    /* 头像 */
    ci.avatar,
    /* 手机号码 */
    mu.mobile,
    /* 注册地址 */
    mu.register_area,
    /* 注册时间 */
    mu.created_at,
    /* 当前所属区域 */
    cl.city,
    /* 所属店铺 */
    sar.shop_name,
    /* 调度区域 */
    cl.location,
    /* 是否开通会员 */
    CASE WHEN cu.is_vip = 1 THEN '是' ELSE '否' END,
    /* 开通会员时间 */
    cu.vip_time,
    /* 一级邀请人 */
    il1.nickname,
    /* 二级邀请人 */
    il2.nickname,
    /* 余额 */
    COALESCE(cw.total_balance, 0),
    /* 状态 */
    cu.state,
    /* 邀请码 */
    cu.invite_code,
    /* 二维码 */
    cu.qr_code,
    /* 认证状态 */
    CASE
        WHEN cq.state = 'APPROVED' THEN '已认证'
        WHEN cq.state = 'PENDING' THEN '认证中'
        WHEN cq.state = 'REJECTED' THEN '认证失败'
        ELSE '未认证'
    END
FROM coach_users cu
LEFT JOIN member_users mu ON cu.user_id = mu.id
LEFT JOIN coach_info_records ci ON cu.info_record_id = ci.id
LEFT JOIN coach_locations cl ON cu.id = cl.coach_id AND cl.type = 'CURRENT'
LEFT JOIN shop_auth_records sar ON cu.shop_id = sar.id
LEFT JOIN coach_qual_records cq ON cu.qualification_record_id = cq.id
LEFT JOIN inviter_level1 il1 ON cu.user_id = il1.user_id
LEFT JOIN inviter_level2 il2 ON cu.user_id = il2.user_id
LEFT JOIN coach_wallet cw ON cu.user_id = cw.owner_id
WHERE cu.deleted_at IS NULL;