我目前正在使用MySQL创建一个视图,该视图将返回以下内容:
NAME | EMAIL | LAST_SEEN
abby | a@l.d | 2015-10-31 14:36:26
abby | a@l.d | 2015-11-28 13:30:37
然后我将GROUP BY name
应用于select query
,它返回以下
NAME | EMAIL | LAST_SEEN
abby | a@l.d | 2015-10-31 14:36:26
我想知道如何修复这个查询,使其返回以下内容:
NAME | EMAIL | LAST_SEEN
abby | a@l.d | 2015-11-28 13:30:37
实际代码如下:
CREATE VIEW v_user_last_seen
AS
SELECT concat_ws(' ', u.first_name, u.middle_name, u.last_name) AS user_name
,c.email
,l.in_when AS last_seen
FROM user AS u
INNER JOIN check_here_first AS c ON c.email = u.email
INNER JOIN log AS l ON l.u_id = c.username
GROUP BY user_name
ORDER BY user_name ASC
只需使用max(lastrongeen)
select name, email, max(last_seen)
from yourtable,
group by name, email;
在分组之前尝试对表进行排序,这应该适用于像下面这样的简单情况:
SELECT *
FROM ( SELECT *
FROM `tablename`
ORDER BY `LAST_SEEN` DESC
) temp
GROUP BY `name`
如果失败,您可以执行以下操作:
SELECT tmp1.*
FROM `tablename` tmp1
LEFT JOIN `tablename` tmp2
ON (tmp1.`name` = tmp2.`name`
AND tmp1.`LAST_SEEN` < tmp2.`LAST_SEEN`)
WHERE tmp2.`name` IS NULL
这里的想法是将表的行与其自身进行匹配,其中匹配的值具有更高的值。对于那些不匹配的,我们将得到一个null,因为我们使用LEFT JOIN
。这些应该是小组中最高的。