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

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test
WITH 
/* 资质证书信息 */
qualification_photos AS (
    SELECT 
        cq.id,
        /* 从业资格证 */
        MAX(CASE WHEN cq.qual_type = 'QUALIFICATION' THEN cq.qual_photo END) AS qualification_photo,
        /* 健康证 */
        MAX(CASE WHEN cq.qual_type = 'HEALTH' THEN cq.qual_photo END) AS health_photo,
        /* 生活照 */
        MAX(CASE WHEN cq.qual_type = 'LIFE_PHOTO' THEN cq.qual_photo END) AS life_photo,
        /* 工作照 */
        MAX(CASE WHEN cq.qual_type = 'WORK_PHOTO' THEN cq.qual_photo END) AS work_photo
    FROM coach_qual_records cq
    WHERE cq.deleted_at IS NULL
    GROUP BY cq.id
)
SELECT 
    /* 技师昵称 */
    ci.nickname,
    /* 性别 */
    ci.gender,
    /* 出生日期 */
    ci.birthday,
    /* 期望城市 */
    ci.intention_city,
    /* 手机号码 */
    ci.mobile,
    /* 个人简介 */
    ci.introduction,
    /* 认证进度 */
    CASE 
        WHEN cu.real_auth_record_id IS NULL THEN '未认证'
        WHEN cu.qualification_record_id IS NULL THEN '实名认证完成'
        ELSE '全部认证完成'
    END,
    /* 认证状态 */
    cu.state,
    /* 身份证照片正面 */
    cr.id_card_front_photo,
    /* 身份证照片反面 */
    cr.id_card_back_photo,
    /* 从业资格证图片 */
    qp.qualification_photo,
    /* 健康证 */
    qp.health_photo,
    /* 生活照 */
    qp.life_photo,
    /* 工作照 */
    qp.work_photo
FROM coach_users cu
LEFT JOIN coach_info_records ci ON cu.info_record_id = ci.id 
LEFT JOIN coach_real_records cr ON cu.real_auth_record_id = cr.id
LEFT JOIN qualification_photos qp ON cu.qualification_record_id = qp.id
WHERE cu.deleted_at IS NULL
    AND ci.deleted_at IS NULL
    AND (cr.deleted_at IS NULL OR cr.deleted_at IS NULL);