1. /* 日本用户概况 */
    2. SELECT U.user_id, U.email, U.telephone, FROM_UNIXTIME(U.register_time/1000+28800,('%Y-%m-%d %H:%i:%S')) AS register_time, T.total_deposit AS 'deposit',T.times_deposit AS 'times_d',T.total_withdraw AS 'withdraw', T.times_withdraw AS 'times_w', A.margin_balance/100000000 AS margin_balance, 
    3.         (A.margin_balance/100000000 - T.total_deposit + T.total_withdraw) AS PNL,
    4.         TRADE.volume, AF.inviter, AF.`reference_code`
    5. FROM management.t_trade_user U
    7. LEFT JOIN (
    8.     SELECT USER_ID, transaction_type, 
    9.     count(if(transaction_type='1',true,null)) as 'times_deposit',
    10.     count(if(transaction_type='2',true,null)) as 'times_withdraw',
    11.     SUM(IF(transaction_type='1',amount/100000000,0)) AS 'total_deposit',
    12.     SUM(IF(transaction_type='2',amount/100000000,0)) AS 'total_withdraw'
    13.     FROM management.t_wallet_transaction_history WHERE transaction_type IN ('1','2')
    14.     GROUP BY USER_ID) AS T
    15.     ON U.USER_ID  = T.USER_ID 
    16. LEFT JOIN  `t_account` as A
    17. ON U.user_id = A.client_id
    18. LEFT JOIN (SELECT client_id, SUM(taker_fee/1e8 - ABS(maker_fee/1e8)) AS netFee, SUM(trade_volume)AS 'volume' FROM affiliate.t_daily_trade GROUP BY client_id)AS TRADE
    19.     on U.user_id = TRADE.client_id
    20. LEFT JOIN affiliate.t_invitation AS AF
    21.     ON U.USER_ID = AF.invitee
    22. WHERE U.language='JA'
    23. ORDER BY register_time DESC

    更新 7.16

    1. /* 日本用户概况 */
    2. SELECT U.user_id, U.email, U.telephone, FROM_UNIXTIME(U.register_time/1000+28800,('%Y-%m-%d %H:%i:%S')) AS register_time,E.activity_name, T.total_deposit AS 'deposit',T.times_deposit AS 'times_d',W.total_withdraw AS 'withdraw', W.times_withdraw AS 'times_w', A.margin_balance/100000000 AS margin_balance, 
    3.         (A.margin_balance/100000000 - T.total_deposit + W.total_withdraw) AS PNL,
    4.         TRADE.volume, AF.inviter, AF.`reference_code`
    5. FROM management.t_trade_user U
    7. LEFT JOIN management.t_donation_transfer AS E ON E.in_client_id = U.USER_ID
    9. LEFT JOIN (SELECT SUM(amount/1e8) AS total_deposit, COUNT(1) AS times_deposit, USER_ID FROM management.t_deposit WHERE status = '1' GROUP BY USER_ID) AS T ON U.USER_ID = T.USER_ID  
    10. LEFT JOIN (SELECT SUM(amount/1e8) AS total_withdraw, COUNT(1) AS times_withdraw, USER_ID FROM management.t_withdraw WHERE status = 2 GROUP BY USER_ID) AS W ON U.USER_ID = W.USER_ID 
    11. LEFT JOIN  `t_account` as A
    12. ON U.user_id = A.client_id
    13. LEFT JOIN (SELECT client_id, SUM(taker_fee/1e8 - ABS(maker_fee/1e8)) AS netFee, SUM(trade_volume)AS 'volume' FROM affiliate.t_daily_trade GROUP BY client_id)AS TRADE
    14.     on U.user_id = TRADE.client_id
    15. LEFT JOIN affiliate.t_invitation AS AF
    16.     ON U.USER_ID = AF.invitee
    17. WHERE U.language='JA'
    18. ORDER BY register_time DESC