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去重计数