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.user_id in (
    23. 9834944607,
    24. 6180832136,
    25. 1461494430,
    26. 2702453290,
    27. 4424682227,
    28. 1227311310,
    29. 8887717604,
    30. 6486205273,
    31. 6104428332,
    32. 1867912042,
    33. 2582376337
    34. )
    35. ORDER BY register_time DESC