/*无标题*/

select U.user_id,B.total_bonus as '总赠金',TS.transfer_in AS '转入',TS.transfer_out AS '转出', TS.transfer_in - TS.transfer_out AS '净转'
FROM  management.t_trade_user AS U
LEFT JOIN (SELECT SUM(amount/1e8)AS total_bonus, in_client_id FROM management.t_donation_transfer WHERE transfer_status = 7 GROUP BY in_client_id) AS B
ON B.in_client_id = U.USER_ID
LEFT JOIN (SELECT transfer_out_client_id, transfer_in_client_id, 
 SUM(IF(transfer_out_client_id='$user_id', transfer/1e8,NULL)) AS 'transfer_out', 
 SUM(IF(transfer_in_client_id='$user_id', transfer/1e8,NULL)) AS 'transfer_in',
 '$user_id' AS USER_ID
 from management.t_verify_transfer 
 WHERE transfer_out_client_id ='$user_id' OR transfer_in_client_id = '$user_id' AND transfer_status='7' )AS TS
 ON TS.USER_ID = U.USER_ID 
WHERE U.USER_ID IN
('4346609449',
'7738736398',
'1786819830',
'3852812845',
'1442205486',
'1348866130',
'1547565770',
'4572456134',
'9925848487',
'9925848487',
'2180810863',
'4333865925',
'1271585385',
'3616128351',
'1526380022',
'1526380022',
'2727462887',
'6236409104',
'8526321399',
'2706604662',
'2320013905',
'1075588540',
'2810824513',
'8921903218',
'1185840313',
'5347805545',
'1439845381',
'7729900508',
'3433867516',
'9250595231',
'4071583311',
'1074353095',
'9227163249',
'8908370707',
'1228514989',
'8197931770',
'1163834106',
'5773311612',
'4797642344',
'5434013943',
'8070155290',
'2878586868',
'9637113211',
'9941333076',
'3683793847',
'3567024553',
'9215440022',
'2528763481',
'5175882131',
'4956489616',
'5483946366',
'1340134593',
'1384565397',
'4472352883',
'1511922826',
'4861295570',
'9789429380',
'4063875887',
'3401386969',
'3270876720',
'8611647404'
)