请你写出一个sql语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序
看到本题的思路是
- 新用户 ==> min(date) 最小日期即为当天新用户
- 新用户个数 ==> count(min(date))
- 每个日期 ==>left join 查询每个日期
- 处理null问题 IFNULL(count,0)
SELECT DISTINCT(a.date), -- IFNULL(count,0) IFNULL(b.new,0) AS new FROM login a -- left join 查询每个日期 LEFT JOIN ( -- count(min(date)) SELECT date,COUNT(1) AS new FROM ( -- min(date) 最小日期即为当天新用户 SELECT user_id,min(date) AS date FROM login GROUP BY user_id )a GROUP BY date )b ON a.date = b.date ORDER BY a.date