mysql - 聚合函数与我查询中的某些列冲突

Question

我有两个表:

users:
 ___________________________
|user_id        | username  |
|_______________|___________|
|  1            |  Dolly    |
|  2            |  Didi     |
|_______________|___________|

forum:
 _____________________________________________________________
|match_static_id| comment   | timpstamp            | user_id  |
|_______________|___________|______________________|__________|
|  1            |  Hi       | 2013-07-10 12:15:03  |     2    |
|  1            |  Hello    | 2013-07-09 12:14:44  |     1    | 
|_______________|___________|______________________|__________|

此查询工作正常，它仅使用 forum 表:

SELECT  forum.match_static_id,
count(forum.match_static_id) 'comments_no', max(forum.timestamp)'timestamp'
FROM forum
GROUP BY forum.match_static_id
Order BY timestamp DESC

但是下面的查询使用了两个表:

SELECT  forum.match_static_id,
count(forum.match_static_id) 'comments_no', max(forum.timestamp)'timestamp', users.username
FROM forum
INNER JOIN users on users.id = forum.user_id
GROUP BY forum.match_static_id

在这里，我想获得 max(timestamp) 的用户，但我得到了错误的用户，任何人都可以给我一些线索吗？按时间戳 DESC 排序

Answer 1

试试这个:

SELECT  f1.match_static_id,
  f2.comments_no, 
  f2.maxtimestamp, 
  users.username
FROM forum AS f1
INNER JOIN
(
  SELECT match_static_id, 
    max(timestamp) maxtimestamp,
    count(comment) AS comments_no
  FROM Forum
  GROUP BY match_static_id
) AS f2  ON f1.match_static_id = f2.match_static_id
        AND f1.timestamp = f2.maxtimestamp
INNER JOIN users on users.user_id = f1.user_id;

在此处查看实际效果:

• SQL Fiddle Demo