这里关键是要筛选出来最近登陆的日期,再去和user、client两个表联结,最近登陆的条件需要特别注意,可以使用(user_id,date)in的方法,也可以使用联结,在联结时需要使用两个条件,即login.date=a.mdate和login.user_id=a.user_id
SELECT user.name AS u_n, client.name AS c_n,login.date AS date FROM login INNER JOIN ( SELECT user_id,MAX(date) AS mdate FROM login GROUP BY user_id) AS a ON login.date=a.mdate AND login.user_id=a.user_id INNER JOIN user ON login.user_id=user.id INNER JOIN client ON login.client_id=client.id ORDER BY user.name