-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test /* 提现角色/渠道是wallet_withdraw_records表中wallet_id关联wallet表获取owner_type字段做为提现角色, 用户名称是wallet_withdraw_records表中wallet_id关联wallet表获取owner_type钱包类型去判断用户角色, 角色有技师角色、用户角色、店铺角色、业务员角色、代理商, 如果owner_type钱包类型为技师角色则通过owner_id继续关联coach_users表判断owner_id与coach_users表id是否相同, 相同则继续通过coach_users表的user_id去member_users表去关联查询出nickname做为用户名称, 如果owner_type钱包类型为用户角色则通过owner_id与member_users表去关联查询出nickname做为用户名称, 如果owner_type钱包类型为店铺角色则通过owner_id与shop_auth_records表shop_id判断是否相同, 相同则获取shop_name做为用户名称,不相同则用户名称空着即可, 如果owner_type钱包类型为业务员角色则通过owner_id与salesmen表id判断是否相同, 相同则获取salesmen表的user_id继续去关联member_users表获取nickname字段做为用户名称, 不相同则用户名空着即可,如果owner_type钱包类型为代理商角色则通过owner_id与agent_infos表id判断是否相同, 相同则获取agent_infos表的user_id继续去关联member_users表获取nickname字段做为用户名称,不相同则用户名称空着即可, 提现金额是wallet_withdraw_records表中amount字段,申请时间是wallet_withdraw_records表中的created_at字段, 提现方式是wallet_withdraw_records表中withdraw_type字段,提现手续费是wallet_withdraw_records表中的fee字段, 审核人是wallet_withdraw_records表中的auditor字段,体现时间是wallet_withdraw_records表中的withdraw_time字段, 体现状态是wallet_withdraw_record表中的audit_state字段,区/县是wallet_withdraw_records表中的area_code字段, 剩余金额是wallet_withdraw_record表通过wallet_id关联wallet表获取available_balance字段做为剩余金额 */ DROP TABLE IF EXISTS report_withdraw_management; CREATE TABLE report_withdraw_management AS /* 提现管理视图 */ DROP VIEW IF EXISTS manage_withdraw; CREATE OR REPLACE VIEW manage_withdraw AS WITH withdraw_detail AS ( SELECT wwr.id /* 提现记录ID */, CASE w.owner_type WHEN 4 THEN ( SELECT mu.nickname FROM coach_users cu LEFT JOIN member_users mu ON cu.user_id = mu.id WHERE cu.id = w.owner_id ) WHEN 3 THEN ( SELECT mu.nickname FROM member_users mu WHERE mu.id = w.owner_id ) WHEN 5 THEN ( SELECT sar.shop_name FROM shop_auth_records sar WHERE sar.id = w.owner_id ) WHEN 7 THEN ( SELECT mu.nickname FROM salesmen s LEFT JOIN member_users mu ON s.user_id = mu.id WHERE s.id = w.owner_id ) WHEN 6 THEN ( SELECT mu.nickname FROM agent_infos ai LEFT JOIN member_users mu ON ai.user_id = mu.id WHERE ai.id = w.owner_id ) END username /* 用户名称 */, w.owner_type /* 提现角色/渠道 */, wwr.amount /* 提现金额 */, wwr.created_at /* 申请时间 */, wwr.withdraw_type /* 提现方式 */, wwr.fee /* 提现手续费 */, wwr.auditor /* 审核人 */, wwr.withdraw_time /* 提现时间 */, wwr.audit_state /* 提现状态 */, wwr.area_code /* 区/县 */, w.available_balance /* 剩余金额 */ FROM wallet_withdraw_records wwr /* 钱包提现记录表 */ LEFT JOIN wallet w ON w.id = wwr.wallet_id /* 钱包表 */ ) SELECT username as username /* 用户名称 */, owner_type as owner_type /* 提现角色/渠道 */, amount as amount /* 提现金额 */, created_at as created_at /* 申请时间 */, withdraw_type as withdraw_type /* 提现方式 */, fee as fee /* 提现手续费 */, auditor as auditor /* 审核人 */, withdraw_time as withdraw_time /* 提现时间 */, audit_state as audit_state /* 提现状态 */, area_code as area_code /* 区/县 */, available_balance as available_balance /* 剩余金额 */ FROM withdraw_detail /* 提现详情临时表 */; select * from manage_withdraw;