按最大(时间)mysql分组 [英] Group by max(time) 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);

这篇关于按最大(时间)mysql分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！