题目
思路
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去重计数