感觉自己写的很啰嗦
1.先按 login 表的 user_id 分组,聚合出最大日期,即是最近登陆日期;
2.然后分组聚合后的表再和 login 做自连接,用复合主键 concat(user_id,'-',date),这样做的目的是为了匹配到最近一次登录时的设备号;
3.上一步的表和 user 表做连接;
4.和 client 表做连接;
5.根据上面几步,已连接成一个包含所需字段的大表,select 出题目需要字段,order by col asc 即可。
select c.name as u_n ,d.name as c_n ,c.date from (select a.user_id ,a.client_id ,b.name ,a.date from (select e.user_id ,f.client_id ,e.date from (select user_id ,max(date) as date from login group by user_id) e inner join login f on concat(e.user_id,'-',e.date)=concat(f.user_id,'-',f.date)) a left join user b on a.user_id=b.id) c left join client d on c.client_id=d.id order by u_n asc