自用题解

忽略了统计数为0的情况(错误写法)

-- 构造出一个新的表,以用户ID分组,然后过滤出每组中最小的日期
-- 根据上表然后在进行统计
-- 出现的问题:没法统计新用户为0的日期
select `new_login`.`date`,count(`new_login`.`id`) as `new`
from 
  (
    select *
    from `login`
    group by `user_id`
    having min(`date`)
  ) as `new_login`
group by `new_login`.`date`
order by `new_login`.`date` asc

union连接写法,这种容易懂一些

SELECT `date`, COUNT(`user_id`) AS `NEW`
FROM `login`
WHERE (`user_id`, `date`) IN
                (SELECT `user_id`, min(`date`)
                 FROM `login`
                 GROUP BY `user_id`)
GROUP BY `date`

UNION

SELECT `date`, 0 AS `NEW`
FROM `login`
WHERE `date` NOT IN(SELECT min(`date`)
                 FROM `login`
                 GROUP BY `user_id`)
ORDER BY `date`;