工作中的问题 - 写了一天的SQL - 《SQL学习》

表结构

CREATE TABLE `t_account_snap` (
  `account_id` bigint(20) NOT NULL COMMENT '资金账号',
  `product_id` varchar(20) DEFAULT NULL COMMENT '产品ID',
  `settlement_id` bigint(20) NOT NULL COMMENT '数据标签',
  `client_id` varchar(30) NOT NULL COMMENT '用户代码',
  `currency` varchar(10) NOT NULL COMMENT '币种',
  `prev_wallet_balance` decimal(30,10) NOT NULL COMMENT '上日钱包余额',
  `wallet_balance` decimal(30,10) NOT NULL COMMENT '钱包余额',
  `available` decimal(30,10) NOT NULL COMMENT '可用余额',
  `margin_balance` decimal(30,10) NOT NULL COMMENT '保证金余额',
  `frozen_margin` decimal(30,10) NOT NULL COMMENT '委托冻结保证金',
  `frozen_available` decimal(30,10) NOT NULL DEFAULT '0.0000000000',
  `current_margin` decimal(30,10) DEFAULT NULL COMMENT '占用保证金(持仓保证金)',
  `affiliate_payout` decimal(30,10) DEFAULT NULL,
  `fee` decimal(30,10) DEFAULT NULL COMMENT '成交手续费',
  `withdraw` decimal(30,10) DEFAULT NULL COMMENT '出金',
  `deposit` decimal(30,10) DEFAULT NULL COMMENT '入金',
  `capital_fee` decimal(30,10) DEFAULT NULL COMMENT '资金费用',
  `realised_pnl` decimal(30,10) NOT NULL COMMENT '已实现盈亏',
  `unrealised_pnl` decimal(30,10) NOT NULL COMMENT '未实现盈亏',
  `no_filed_cnt` bigint(20) DEFAULT NULL,
  `sell_vol_sum` bigint(20) DEFAULT NULL,
  `buy_vol_sum` bigint(20) DEFAULT NULL,
  `sell_cost` decimal(30,10) DEFAULT NULL,
  `buy_cost` decimal(30,10) DEFAULT NULL,
  `transfer` decimal(30,10) DEFAULT NULL COMMENT '今日转账',
  `commission` decimal(30,10) NOT NULL COMMENT '交易手续费',
  `withdraw_fee` decimal(30,10) DEFAULT NULL COMMENT '提现手续费(比特币网络费用)',
  `transfer_account` decimal(30,10) DEFAULT NULL COMMENT '（同用户资金账号间）划转',
  `transfer_client` decimal(30,10) DEFAULT NULL COMMENT '（不同用户间）今日转账',
  `largess` decimal(30,10) DEFAULT NULL COMMENT '赠币',
  `compensation` decimal(10,0) DEFAULT NULL COMMENT '补偿',
  `kafka_partition` int(11) NOT NULL,
  `kafka_offset` bigint(20) DEFAULT NULL,
  `insert_time` bigint(20) DEFAULT NULL COMMENT '插入时间',
  PRIMARY KEY (`account_id`,`settlement_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='账户结算时资金快照';

CREATE TABLE `t_settlement` (
  `account_id` bigint(20) NOT NULL COMMENT '资金账号',
  `client_id` varchar(30) NOT NULL COMMENT '用户代码',
  `settlement_id` bigint(20) NOT NULL COMMENT '结算ID',
  `currency` varchar(10) NOT NULL COMMENT '币种',
  `prev_wallet_balance` decimal(30,10) NOT NULL COMMENT '上日钱包余额',
  `wallet_balance` decimal(30,10) NOT NULL COMMENT '钱包余额',
  `realised_gross_pnl` decimal(30,10) NOT NULL COMMENT '平仓盈亏',
  `frozen_available` decimal(30,10) NOT NULL COMMENT '冻结资金',
  `realised_pnl` decimal(30,10) NOT NULL COMMENT '已实现盈亏',
  `withdraw` decimal(30,10) DEFAULT NULL COMMENT '出金',
  `deposit` decimal(30,10) DEFAULT NULL COMMENT '入金',
  `fee` decimal(30,10) DEFAULT NULL COMMENT '成交手续费',
  `capital_fee` decimal(30,10) DEFAULT NULL COMMENT '资金费用',
  `withdraw_fee` decimal(30,10) DEFAULT NULL COMMENT '提现手续费(比特币网络费用)',
  `transfer` decimal(30,10) DEFAULT NULL COMMENT '今日转账',
  `transfer_account` decimal(30,10) DEFAULT NULL COMMENT '（同用户资金账号间）划转',
  `transfer_client` decimal(30,10) DEFAULT NULL COMMENT '（不同用户间）今日转账',
  `affiliate_payout` decimal(30,10) DEFAULT NULL COMMENT '返佣',
  `largess` decimal(30,10) DEFAULT NULL COMMENT '赠币',
  `compensation` decimal(30,10) DEFAULT NULL COMMENT '补偿',
  `kafka_partition` int(11) NOT NULL,
  `kafka_offset` bigint(20) NOT NULL,
  `insert_time` bigint(20) NOT NULL COMMENT '插入时间',
  PRIMARY KEY (`account_id`,`settlement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

写了一半了

/*比赛时间：北京时间9月8日4:00-9月22日4:00*/
/*今日收益率=  ( 当前保证金余额 - 前一日结算后保证金余额 - 今日截止到当前的出金+今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = 100% * ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 - 截止到前一日结算时的出入金 ) / ( 比赛开始时的保证金余额 + 截止到前一日结算时的入金 )*/
SELECT c.client_id AS '客户ID',c.margin_balance/100000000 AS '账户净值(保证金余额)',c.wallet_balance/100000000 AS'钱包余额' ,sum(c.realised_pnl+P.realised_pnl)/100000000 AS '已实现盈亏',pmb.margin_balance/100000000 AS '前一日结算后保证金余额',(c.margin_balance-pmb.margin_balance-W.withdraw+d.deposit)/(pmb.margin_balance+d.deposit)AS'今日收益率'
FROM clear.t_account as c
/*已实现盈亏是已结算的PNL，加上今日的*/
LEFT JOIN (select client_id,sum(realised_pnl)as realised_pnl from clear.t_settlement where settlement_id>=20200908040000)P
ON P.client_id=c.client_id
/*前一日结算后保证金余额*/
LEFT JOIN (SELECT client_id,margin_balance FROM t_account_snap where client_id=4395759580 order by settlement_id desc limit 1,1)pmb
ON pmb.client_id=c.client_id
/*今日截止到当前的出金，今日这个条件想下咋写*/
LEFT JOIN (SELECT user_id,sum(amount+fee)/100000000 as withdraw From management.t_withdraw )W
ON W.user_id=c.client_id
/*今日截止到当前的入金，今日这个条件想下咋写*/
LEFT JOIN (SELECT user_id,sum(amount)/100000000 as deposit From management.t_deposit)d
ON d.user_id=c.client_id
where c.client_id=4395759580

目前问题：
1.截至今日，这个今日是以12点为分界线，上午，NOW()-昨日12点；下午，NOW（）-今日12点，这个咋写
2.比赛开始时的保证金余额，dump数据库
3.截止到前一日结算时的出入金一样的问题，更复杂

更新：
1.因为技术上做不到（不想做）在时间上切片取快照，改了规则改为12点
2.改变规则解决了【已实现盈亏】【今日出入金】筛选的问题

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到前一日结算时的出金-+截止到前一日结算时的入金 ) / ( 比赛开始时的保证金余额 + 截止到前一日结算时的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (C.margin_balance-PMB.pmargin_balance+W.withdraw-D.deposit)/(PMB.pmargin_balance+D.deposit) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+TW.twithdraw-TD.tdeposit)/(BMB.bmargin_balance+TD.tdeposit) AS'累计收益率'
FROM clear.t_account AS C
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200908120000) P
ON P.client_id = C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
                    ORDER BY settlement_id DESC    
                    LIMIT 1,1) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE op_time>=(NOW()-interval 24 hour)) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE op_time>=(NOW()-interval 24 hour)) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=202009070000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE op_time BETWEEN '2020/09/08 12:00:00' AND NOW()) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE op_time BETWEEN '2020/09/08 12:00:00' AND NOW()) TD
ON TD.user_id=C.client_id
WHERE C.client_id =4395759580

更新2.0
数据库NOW（）需要+8；
出入金要限制状态；

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (NMB.nmargin_balance-PMB.pmargin_balance+W.withdraw-D.deposit)/(PMB.pmargin_balance+D.deposit) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+TW.twithdraw-TD.tdeposit)/(BMB.bmargin_balance+TD.tdeposit) AS'累计收益率'
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200908120000) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    ORDER BY settlement_id DESC    
                    LIMIT 1) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
                    ORDER BY settlement_id DESC    
                    LIMIT 1,1) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200907120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)) TD
ON TD.user_id=C.client_id
WHERE C.client_id =4395759580

更新3.0 实操数据显示不出来
在子查询中加入GROUP BY USER_ID

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (NMB.nmargin_balance-PMB.pmargin_balance+W.withdraw-D.deposit)/(PMB.pmargin_balance+D.deposit) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+TW.twithdraw-TD.tdeposit)/(BMB.bmargin_balance+TD.tdeposit) AS'累计收益率'
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200908120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    GROUP BY client_id
                    ORDER BY settlement_id DESC    
                    LIMIT 1) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
                    GROUP BY client_id
                    ORDER BY settlement_id DESC    
                    LIMIT 1,1) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200907120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id
WHERE C.client_id =4395759580

备份0907

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (NMB.nmargin_balance-PMB.pmargin_balance+W.withdraw-D.deposit)/(PMB.pmargin_balance+D.deposit) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+TW.twithdraw-TD.tdeposit)/(BMB.bmargin_balance+TD.tdeposit) AS'累计收益率',
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200902120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    GROUP BY client_id
                    ORDER BY settlement_id DESC    
                    LIMIT 1) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
                    GROUP BY client_id
                    ORDER BY settlement_id DESC    
                    LIMIT 1,1) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200903120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/01 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/01 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id
WHERE C.client_id =3117719576

一部分做出来的时间戳：

select DATE_FORMAT(now(),'%Y%m%d')as A
from dual;

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (NMB.nmargin_balance-PMB.pmargin_balance+W.withdraw-D.deposit)/(PMB.pmargin_balance+D.deposit) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+TW.twithdraw-TD.tdeposit)/(BMB.bmargin_balance+TD.tdeposit) AS'累计收益率',
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200907120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-1000000
                    GROUP BY client_id
                    ) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
          where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-2000000
                    GROUP BY client_id
                    ) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200907120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id

WHERE C.client_id =3117719576

更新settlement_id时间戳

select DATE_FORMAT(now(),'%Y%m%d120000')as A
from dual;

deposit是个null 然后今日收益率就算不出来
查询中设置默认值

ifnull(字段,0)

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT C.client_id AS'用户ID',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
            (NMB.nmargin_balance-PMB.pmargin_balance+IFNULL(W.withdraw,0)-IFNULL(D.deposit,  0))/(PMB.pmargin_balance+IFNULL(D.deposit,  0)) AS '今日收益率',
            (PMB.pmargin_balance-BMB.bmargin_balance+IFNULL(TW.twithdraw,0)-IFNULL(TD.tdeposit,0))/(BMB.bmargin_balance+IFNULL(TD.tdeposit,0)) AS'累计收益率',
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200902120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-1000000
                    GROUP BY client_id
                    ) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
          where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-2000000
                    GROUP BY client_id
                    ) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200903120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/01 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/01 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id

WHERE C.client_id IN ('7872312363',
'2566903148',
'8786505148',
'1628150501',
'3832946905',
'6238764499',
'7627877710',
'4705173630',
'1602961166',
'9548152120',
'2020400638',
'9045796617',
'7379086792',
'8634529568',
'1685000358',
'5933028568',
'2976894010',
'2254375159',
'2938016278',
'5378220025',
'2210014547',
'4087278399',
'2678060630',
'4509231523')

最终版本

/*比赛时间：北京时间9月8日12:00-9月22日12:00*/
/*今日收益率=  ( 当前结算日保证金余额 - 前一日结算后保证金余额 +今日截止到当前的出金-今日截止到当前的入金 ) / ( 前一日结算后保证金余额 + 今日截止到当前的入金 )*/
/*累计收益率 = ( 前一日结算后保证金余额 - 比赛开始时的保证金余额 +截止到当前的出金-截止到当前的入金 ) / ( 比赛开始时的保证金余额 + 截止到当前的入金 )*/
SELECT CONCAT(LEFT(C.client_id,3), '****' ,RIGHT(C.client_id,3))   AS'用户ID', 
            IFNULL(DAILY_TRADE.traded,0)  AS '累计交易量',
            CONCAT( ROUND((PMB.pmargin_balance-BMB.bmargin_balance+IFNULL(TW.twithdraw,0)-IFNULL(TD.tdeposit,0))/(BMB.bmargin_balance+IFNULL(TD.tdeposit,0))  * 100, 2 ), '', '%' )AS '累计收益率',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
        (PMB.pmargin_balance-BMB.bmargin_balance+IFNULL(TW.twithdraw,0)-IFNULL(TD.tdeposit,0))/(BMB.bmargin_balance+IFNULL(TD.tdeposit,0)) AS 'X累',
            (NMB.nmargin_balance-PMB.pmargin_balance+IFNULL(W.withdraw,0)-IFNULL(D.deposit,  0))/(PMB.pmargin_balance+IFNULL(D.deposit,  0)) AS '今日收益率',
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit
FROM clear.t_account AS C
/*用户邮箱手机
LEFT JOIN   management.t_trade_user as U 
ON U.user_id=C.client_id*/
/*已实现盈亏*/
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement
                    /*9月8日的对账ID是0907，已实现盈亏从9日开始*/
                    where settlement_id>=20200908120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
/*当前结算日保证金余额NMB.nmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-1000000
                    GROUP BY client_id
                    ) NMB
ON NMB.client_id=C.client_id
/*前一日结算后保证金余额PMB.pmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
          where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-2000000
                    GROUP BY client_id
                    ) PMB
ON PMB.client_id=C.client_id
/*今日出金，包含手续费W.withdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) W
ON W.user_id=C.client_id
/*今日入金D.deposit*/ /*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time>=(DATE_ADD(now(), INTERVAL 8 HOUR)-interval 24 hour)
                    GROUP BY user_id) D
ON D.user_id=C.client_id
/*比赛开始时保证金余额BMB.bmargin_balance*/
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap
                    /*9月8日的对账ID是0907*/
                    WHERE settlement_id=20200907120000) BMB
ON BMB.client_id=C.client_id
/*比赛截止到此时出金，包含手续费TW.twithdraw*/ /* 出金状态status=123，为正常出金 */
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
/*比赛截止到此时入金TD.tdeposit*//*入金状态status=1，为充值成功*/
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_ADD(now(), INTERVAL 8 HOUR)
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id
/*交易量*/
LEFT JOIN
                (SELECT SUM(trade_volume)AS traded, client_id 
                FROM affiliate.t_daily_trade AS TRADED 
                /*9月8日的对账ID是0907*/
                WHERE settlement_id >= 20200908120000
                GROUP BY client_id)  DAILY_TRADE 
ON DAILY_TRADE.client_id=C.client_id

WHERE C.client_id IN ('2312671512',
'9013294786')/*改成比赛参加者ID*/

比赛排名SQL2.0无注释版带用户ID.txt

SELECT C.client_id,
            CONCAT(LEFT(C.client_id,3), '****' ,RIGHT(C.client_id,3))   AS'用户ID', 
            IFNULL(DAILY_TRADE.traded,0)  AS '累计交易量',
            CONCAT( ROUND((NMB.nmargin_balance-BMB.bmargin_balance+IFNULL(TW.twithdraw,0)-IFNULL(TD.tdeposit,0)-IFNULL(TC.tcommission,0))/(BMB.bmargin_balance+IFNULL(TD.tdeposit,0)-IFNULL(TC.tcommission,0))  * 100, 2 ), '', '%' )AS '累计收益率',
            CONCAT( ROUND((NMB.nmargin_balance-PMB.pmargin_balance+IFNULL(W.withdraw,0)-IFNULL(D.deposit,  0)-IFNULL(CC.commission,0))/(PMB.pmargin_balance+IFNULL(D.deposit,  0)+IFNULL(CC.commission,0))  * 100, 2 ), '', '%' )AS '今日收益率',
            C.margin_balance/100000000 AS'账户净值',
            C.wallet_balance/100000000 AS'钱包余额',
            P.pnl AS'已实现盈亏',
        (NMB.nmargin_balance-BMB.bmargin_balance+IFNULL(TW.twithdraw,0)-IFNULL(TD.tdeposit,0)-IFNULL(TC.tcommission,0))/(BMB.bmargin_balance+IFNULL(TD.tdeposit,0)-IFNULL(TC.tcommission,0)) AS 'X累',
            (NMB.nmargin_balance-PMB.pmargin_balance+IFNULL(W.withdraw,0)-IFNULL(D.deposit,  0)-IFNULL(CC.commission,0))/(PMB.pmargin_balance+IFNULL(D.deposit,  0)+IFNULL(CC.commission,0)) AS 'X金',
            NMB.nmargin_balance,PMB.pmargin_balance,W.withdraw,D.deposit,BMB.bmargin_balance,TW.twithdraw, TD.tdeposit,CC.commission,TC.tcommission
FROM clear.t_account AS C
LEFT JOIN 
                (SELECT client_id,sum(realised_pnl)/100000000 as pnl
                    FROM clear.t_settlement

                    where settlement_id>=20200908120000
                    GROUP BY client_id) P
ON P.client_id = C.client_id
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as nmargin_balance
                    FROM clear.t_account_snap
                    where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-1000000
                    GROUP BY client_id
                    ) NMB
ON NMB.client_id=C.client_id
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as pmargin_balance
                    FROM clear.t_account_snap
          where settlement_id =DATE_FORMAT(now(),'%Y%m%d120000')-2000000
                    GROUP BY client_id
                    ) PMB
ON PMB.client_id=C.client_id
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as withdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN DATE_FORMAT(now(),'%Y%m%d120000')-1000000 AND DATE_FORMAT(now(),'%Y%m%d120000')
                    GROUP BY user_id) W
ON W.user_id=C.client_id
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as deposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN DATE_FORMAT(now(),'%Y%m%d120000')-1000000 AND DATE_FORMAT(now(),'%Y%m%d120000')
                    GROUP BY user_id) D
ON D.user_id=C.client_id
LEFT JOIN 
                (SELECT client_id,margin_balance/100000000 as bmargin_balance
                    FROM clear.t_account_snap

                    WHERE settlement_id=20200907120000) BMB
ON BMB.client_id=C.client_id
LEFT JOIN
                (SELECT user_id,sum(amount+fee)/100000000 as twithdraw
                    FROM management.t_withdraw
                    WHERE status <=3
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_FORMAT(now(),'%Y%m%d120000')
                    GROUP BY user_id) TW
ON TW.user_id=C.client_id
LEFT JOIN
                (SELECT user_id,sum(amount)/100000000 as tdeposit
                    FROM management.t_deposit
                    WHERE status=1
                    AND op_time BETWEEN '2020/09/08 12:00:00' AND DATE_FORMAT(now(),'%Y%m%d120000')
                    GROUP BY user_id) TD
ON TD.user_id=C.client_id
LEFT JOIN
                (SELECT SUM(trade_volume)AS traded, client_id 
                FROM affiliate.t_daily_trade AS TRADED 

                WHERE settlement_id >= 20200908120000
                GROUP BY client_id)  DAILY_TRADE 
ON DAILY_TRADE.client_id=C.client_id
LEFT JOIN 
             (SELECT inviter, SUM(commission_amount)/100000000 as commission
                FROM affiliate.t_affiliate_commission_transfer 
                where settlement_id=DATE_FORMAT(now(),'%Y%m%d120000')-2000000
                GROUP BY inviter)AS CC
ON  CC.inviter = C.client_id
LEFT JOIN 
             (SELECT inviter, SUM(commission_amount)/100000000 as tcommission
                FROM affiliate.t_affiliate_commission_transfer 
                where settlement_id>=20200907120000
                AND settlement_id<DATE_FORMAT(now(),'%Y%m%d120000')-1000000
                GROUP BY inviter)AS TC
ON  TC.inviter = C.client_id
WHERE C.client_id IN ( '9849494410',
'9720581973',
'9665113307',
'9647973682',
'9599963664',
'9348380136',
'9306222374',
'9295378292',
'9134633884',
'9048292968',
'9045796617',
'9013294786',
'8972825756',
'8817470314',
'8786505148',
'8715150495',
'8672560644',
'8634529568',
'8500293002',
'8443717217',
'8142035273',
'7995204686',
'7652669014',
'7627877710',
'7603211109',
'7579235478',
'7379086792',
'7129100137',
'7081302493',
'6906376611',
'6789138099',
'6636518358',
'6486205273',
'6462097783',
'6458416388',
'6436230460',
'6413409329',
'6365586556',
'6318458293',
'6238764499',
'6236409104',
'6188471486',
'6024984902',
'5933028568',
'5794048820',
'5679194986',
'5521839867',
'5380320005',
'5305451442',
'5281033009',
'5164484424',
'5118915377',
'5083897688',
'5047792622',
'4928730669',
'4705173630',
'4702547253',
'4602779671',
'4572456134',
'4569013961',
'4509231523',
'4341795720',
'4316989699',
'4266869864',
'4206506893',
'3932518946',
'3852812845',
'3832946905',
'3590232764',
'3521756755',
'3401386969',
'3351508094',
'3197972102',
'3151316749',
'3111252143',
'3053769232',
'3012458049',
'2976894010',
'2974630951',
'2967547944',
'2938016278',
'2925420281',
'2832154241',
'2764431012',
'2702453290',
'2582376337',
'2566903148',
'2254375159',
'2087633378',
'2063471994',
'2047520786',
'2020400638',
'1992747394',
'1894510829',
'1867912042',
'1835581499',
'1820822957',
'1817178822',
'1784862406',
'1777825447',
'1776273868',
'1685000358',
'1684835535',
'1653837086',
'1628150501',
'1602961166',
'1584936768',
'1583019548',
'1526380022',
'1437922885',
'1285773185',
'1270726791',
'1180797857',
'1082365856',
'1017970362',
'1013086083')