我有这张桌子

user
  id
  name

visit
  id
  id_user (fk user.id)
  date
  comment

如果我执行此查询,

SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id

我明白了

1 Jhon 1 2013-12-01 '1st Comment' 
1 Jhon 2 2013-12-03 '2nd Comment' 
1 Jhon 3 2013-12-01 '3rd Comment'

如果我GROUP BY u.id,那么我得到

1 Jhon 1 2013-12-01 '1st Comment'

我需要俊的最后一次拜访

1 Jhon 3 2013-12-04 '3rd Comment'

我尝试这个

SELECT u.id, u.name, e.id, MAX(e.date), e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
GROUP BY u.id

和这个,

SELECT u.id, u.name, e.id, MAX(e.date), e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
GROUP BY u.id 
HAVING MAX(e.date)

我得到

1 Jhon 1 2013-12-04 '1st Comment'

但这对我无效…我需要该用户的最后一次访问

1 Jhon 3 2013-12-01 '3rd Comment'

谢谢！

解决方法:

这应该给您每个用户的最后评论：

SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN (SELECT t1.*
  FROM visit t1
    LEFT JOIN visit t2
      ON t1.id_user = t2.id_user AND t1.date < t2.date
  WHERE t2.id_user IS NULL
  ) e ON e.id_user=u.id