按最大(时间)mysql分组

本文介绍了按最大(时间)mysql分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

首先，这是对以下内容的重复: 通过具有最大日期来组 我发布问题是因为接受的答案对我不起作用，我也不知道为什么.我的问题:

First of all this is kind of a duplicate to: GROUP BY having MAX date I am posting the question because the accepted answer doesn't work for me and I have no idea why. My problem:

我想选择所有功能(func_ids)的最新(max(timestamp))校验和.

I want to select the latest (max(timestamp)) checksum of all functions (func_ids).

@Bill Karwin的代码(可接受的答案)

The code from @Bill Karwin (accepted answer)

SELECT func_id,checksum FROM Content cnt INNER JOIN ( SELECT func_id, MAX(timestamp) AS maxdate FROM Content GROUP BY func_id ) AS max USING (func_id,maxdate);

Mysql错误: #1054 - Unknown column 'maxdate' in 'from clause'

Mysql error: #1054 - Unknown column 'maxdate' in 'from clause'

我的桌子:

CREATE TABLE `Content` ( `id` int(11) NOT NULL AUTO_INCREMENT, `func_id` int(6) NOT NULL, `description` text CHARACTER SET utf8 NOT NULL, `returns` varchar(255) CHARACTER SET utf8 NOT NULL, `var` varchar(255) CHARACTER SET utf8 NOT NULL, `content` text CHARACTER SET utf8 NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `checksum` varchar(40) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`), KEY `func_id` (`func_id`), KEY `var` (`var`), KEY `checksum` (`checksum`), FULLTEXT KEY `description` (`description`) ) ENGINE=MyISAM AUTO_INCREMENT=885 DEFAULT CHARSET=latin1

推荐答案

据我了解，当您将USING用作内部联接时，MySQL的含义是，两个表中的列都必须命名相同.内容表上没有名为maxdate的列，因此错误会跳转.您可能可以尝试(如果我理解正确的话)

As I understand the sintaxis from MySQL when you put USING for an Inner Join the columns need to be named the same in both tables. There is no column named maxdate on the content table so the error jumps. You may be able to try (if I understand things correctly)

SELECT func_id,checksum FROM Content cnt INNER JOIN ( SELECT func_id, MAX(timestamp) AS maxdate FROM Content GROUP BY func_id ) AS max ON (cnt.func_id=max.func_id AND max.maxdate=cnt.timestamp);