SQL | 表自连接 | 每个日期登录新用户个数

题目

https://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a?tpId=82&tqId=35081&rp=1&ru=%2Factivity%2Foj&qru=%2Fta%2Fsql%2Fquestion-ranking

思路

select l1.date,count(distinct case when l2.user_id IS null then l1.user_id else null end)
from login l1 left join login l2
on l1.user_id=l2.user_id
and l1.date>l2.date
group by l1.date
order by l1.date

表自连接

mysql> select * from login l1 left join login l2 on l1.user_id=l2.user_id and l1.date>l2.date;
+----+---------+-----------+------------+------+---------+-----------+------------+
| id | user_id | client_id | date       | id   | user_id | client_id | date       |
+----+---------+-----------+------------+------+---------+-----------+------------+
|  1 |       2 |         1 | 2020-10-12 | NULL |    NULL |      NULL | NULL       |
|  2 |       3 |         2 | 2020-10-12 | NULL |    NULL |      NULL | NULL       |
|  3 |       1 |         2 | 2020-10-12 | NULL |    NULL |      NULL | NULL       |
|  4 |       2 |         2 | 2020-10-13 |    1 |       2 |         1 | 2020-10-12 |
|  5 |       1 |         2 | 2020-10-13 |    3 |       1 |         2 | 2020-10-12 |
|  6 |       3 |         1 | 2020-10-14 |    2 |       3 |         2 | 2020-10-12 |
|  7 |       4 |         1 | 2020-10-14 | NULL |    NULL |      NULL | NULL       |
|  8 |       4 |         1 | 2020-10-15 |    7 |       4 |         1 | 2020-10-14 |
+----+---------+-----------+------------+------+---------+-----------+------------+

第一个重点：

l1.user_id=l2.user_id 
and l1.date>l2.date 
 # 此处要用and，条件在on后面，不满足条件的会显示为NULL。用where的话条件归为where后面，不满足条件的会被过滤掉。

第二个重点：

case when l2.user_id IS null then l1.user_id else null end
# then 返回l1.user_id，目的为user_id去重计数