mysql - 查找重复行仅获取较旧的行

Question

我有这个查询，它将给我那些具有重复字段“用户名”的行:

SELECT id, jos_users.username, email, password, lastvisitDate 
FROM jos_users
INNER JOIN (SELECT username 
            FROM jos_users
            GROUP BY username HAVING count(id) > 1) dup 
           ON jos_users.username = dup.username;

我需要获取那些上次访问日期较低的人。

例如:

id  |  username  |  email  |  password  |      lastvisitDate      |
1   |  mylogin   |         |            |   2014-10-15 16:42:42   |
2   |  mylogin   |         |            |   2014-10-16 16:42:42   |

如您所见，id=1 的行具有最低的lastvisitDate。我怎样才能把这句话放在查询中？我想要这个，因为我将使用此选择执行删除查询来删除重复的行。

Answer 1

SELECT id, jos_users.username, email, password, lastvisitDate
  FROM jos_users
 INNER JOIN (SELECT username
               FROM jos_users jb
              where jb.lastvisitDate =
                    (select min(jb1.lastvisitDate)
                       from jos_users jb1
                      where jb1.username = jb.username)
              GROUP BY username
             HAVING count(id) > 1) dup
    ON jos_users.username = dup.username;