select date,sum(case when (user_id,date) in (select user_id,min(date) date_1 from login group by user_id)
then 1 else 0 end ) new from login group by date order by date
#要擅于把问题转化为0，1问题，这样才能把case语句用好！