owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/13平台收支.sql

-- Active: xiaoding_test@@192.168.110.85@3306@xiaoding_test

/*
交易时间是wallet_trans_records表判断wallet_id字段值为1然后获取trans_time字段做为交易时间，
收入渠道是wallet_trans_records表判断wallet_id字段值为1然后获取owner_type字段做为收入渠道，
省份、城市、地区以及平台收入字段都是wallet_trans_records表判断wallet_id字段值为1然后分别获取province、city、district、amount字段即可
*/

/* 查询平台收入明细 */
DROP TABLE IF EXISTS report_platform_income;

CREATE TABLE report_platform_income AS

DROP VIEW IF EXISTS manage_platform_income;

CREATE OR REPLACE VIEW manage_platform_income AS
SELECT
    wtr.role as role /* 交易角色 */,
    wtr.trans_type as trans_type /* 交易类型 */,
    trans_time as trans_time /* 交易时间 */,
    owner_type as owner_type /* 业务类型 */,
    wtr.storage_type as storage_type /* 存储类型 */,
    wtr.before_recharge_balance as before_recharge_balance /* 交易前余额 */,
    amount as amount /* 平台收入 */,
    wtr.after_recharge_balance as after_recharge_balance /* 交易后余额 */,
    wtr.state as state /* 交易状态 */,
    province as province /* 省份 */,
    city as city /* 城市 */,
    district as district /* 地区 */,
    wtr.remark as remark /* 备注 */
FROM wallet_trans_records wtr /* 钱包交易记录表 */
WHERE
    wtr.wallet_id = 9 /* 平台钱包ID */
    AND wtr.deleted_at IS NULL /* 未删除的记录 */
ORDER BY wtr.trans_time DESC /* 按交易时间倒序排序 */;

select * from manage_platform_income;