select
cu.reg_mobile mobile,
substring_index(group_concat(ci.updated_user order by ci.updated_time desc), ',',1) as updatedUser,
substring_index(group_concat(ci.remark order by ci.updated_time desc), ',',1) as remark,
sum(ci.score) obtainScoreSum,
sum(<![CDATA[case when ci.score > 0 then ci.score else 0 end]]>) scoreSum,
sum(<![CDATA[case when ci.score < 0 then ci.score else 0 end]]>) usedScoreSum,
count(<![CDATA[case when ci.score < 0 then ci.score else null end]]>) usedScoreNum,
sum(case when ci.happen_way = 'SYS_GIFT' then ci.score else 0 end) sendScoreSum,
count(ci.happen_way = 'SYS_GIFT' or null) sendScoreNum
from t_vcar_cus_integral ci
left join t_vcar_customer cu on cu.id = ci.customer_id
group by cu.reg_mobile
having 1=1
