*方案1:login表+dense_rank窗口函数按date排序作为临时表t;联结user,client表,where过滤t.date_rank获得每个用户最近一次登录的记录
SELECT u.name AS u_n,c.name AS c_n,t.date FROM (SELECT *,DENSE_RANK() OVER(PARTITION BY user_id ORDER BY date DESC) AS date_rank FROM login) t INNER JOIN user u ON t.user_id = u.id INNER JOIN client c ON t.client_id = c.id WHERE t.date_rank = 1 ORDER BY u.name ASC;
*方案2:同样三表联结,where子查询过滤,过滤的子句采用MAX()函数求出date最大并传递到子查询之外。
SELECT u.name AS u_n,c.name AS c_n,l1.date FROM login l1 INNER JOIN user u ON l1.user_id = u.id INNER JOIN client c ON l1.client_id = c.id WHERE l1.date IN (SELECT MAX(l2.date) FROM login l2 WHERE l1.user_id = l2.user_id GROUP BY l2.user_id) ORDER BY u.name ASC;