select date,count(last_date is null or null) new 
from (
    select t1.user_id,t1.date,t2.date last_date 
    from login t1 
    left join login t2 
    on t1.user_id =t2.user_id 
    and t1.date>t2.date 
)temp  
group by date  
order by date

主要是temp这个联连接查询，他能直接给出当前日期有没有往日登录的记录， t1.user_id =t2.user_id
and t1.date>t2.date