SELECT city.`name` '城市', user.id '用户ID', real_name as '姓名', nick_name as '昵称',
c.count as '发车量',
cvl.count as '查看量',
cl.count '拨打量',
ull.count '登录次数'

FROM `user` 
LEFT JOIN city  ON city.id = `user`.city
LEFT JOIN (SELECT creater_id, count(1) count FROM cars GROUP BY creater_id) c ON c.creater_id = `user`.id
LEFT JOIN (SELECT user_id, count(1) count FROM car_view_log GROUP BY user_id ) cvl ON cvl.user_id = `user`.id
LEFT JOIN (SELECT userid, count(1) count FROM contact_log GROUP BY userid) cl  on cl.userid = `user`.id
LEFT JOIN (SELECT userid, count(1) as count FROM user_login_log  GROUP BY userid)ull ON ull.userid = `user`.id

WHERE `user`.invite_status=1 AND (city.`name`='长沙' OR city.`name` = '武汉')

GROUP BY user.id;
SELECT city.name,u.nick_name 昵称,u.real_name 姓名,u.telephone 电话,u.shop_name 门店,u.address 地址, c.car_count 在售车源,
CASE  WHEN u.pay_status>0 THEN '正式会员' ELSE '体验会员' END AS 类型,
u1.real_name 邀请人,
u2.real_name 运营人,
login.login_count 登录次数,
u.lastlogin_at 最后登录,FROM_UNIXTIME(u.invite_checktime) 开通时间, FROM_UNIXTIME(u.expired_at) 到期时间
 FROM `user` u 
LEFT JOIN(SELECT creater_id,count(1) car_count FROM cars WHERE car_status=99  GROUP BY creater_id) c ON u.id=c.creater_id 
LEFT JOIN(SELECT userid,count(1) login_count FROM `user_login_log` GROUP BY userid) login ON login.userid=u.id 
LEFT JOIN city ON city.id =u.city


WHERE city.name='武汉' AND u.invite_status=1 ORDER BY 开通时间 DESC;