MySQL 查询 : Select latest record where column contains certain criteria

Question

我有一个表，我需要查询该表以获取其中描述包含某些数据的最新记录。表列包含(部分)以下内容:

+-----------+------------+-------------------+
| AccountID |    Date    |    Description    |
+-----------+------------+-------------------+
|    125060 | 2006-02-11 | Red Apple         |
|    125060 | 2007-03-23 | Yellow Banana     |
|    125060 | 2009-04-03 | Yellow Apple      |
|    125687 | 2006-03-10 | Red Apple         |
|    139554 | 2007-06-29 | Orange Orange     |
|    139554 | 2009-07-24 | Green Apple       |
|    145227 | 2008-11-22 | Green Pear        |
|    145227 | 2012-04-16 | Yellow Grapefruit |
|    154679 | 2014-05-22 | Purple Grapes     |
|    163751 | 2012-02-11 | Green Apple       |
|    ...    |    ...     |       ...         |
+-----------+------------+-------------------+

(还有几列，几十万条记录，但这就是我目前感兴趣的)

对于此示例，我想要包含“Apple”的 AccountID 子集的最新记录。我正在寻找的结果是:

+-----------+------------+--------------+
| AccountID |    Date    | Description  |
+-----------+------------+--------------+
|    125060 | 2009-04-03 | Yellow Apple |
|    125687 | 2006-03-10 | Red Apple    |
|    139554 | 2009-07-24 | Green Apple  |
+-----------+------------+--------------+

我当前使用的查询是:

SELECT AccountID, max(Date), Description 
FROM products
WHERE Description like "%Apple%" and (AccountID=125060 or AccountID=125687 or AccountID=139554)
GROUP BY AccountID;

不幸的是，结果是这样的:

+-----------+------------+-------------------+
| AccountID |    Date    |    Description    |
+-----------+------------+-------------------+
|    125060 | 2009-04-03 | Red Apple         |
|    125687 | 2006-03-10 | Red Apple         |
|    139554 | 2009-07-24 | Green Apple       |
+-----------+------------+-------------------+

其中 AccountID 已正确分组，并且正在选择适当的(最近的)日期，但描述仍然返回与 WHERE/like 子句匹配的第一个描述...而不是与记录相关的描述与选定的日期。

我以前从未见过这样的事情。这是我做错了什么吗？我对高级 MySQL 查询没有丰富的经验，但这是否更适合子查询上的左联接或内联接？

我考虑首先使用子查询来提取描述中包含所需文本的所有记录，然后查询该子查询以按最近的选择/分组，但不知道这是否有必要。

提前非常感谢您的帮助!

更新该服务器主机正在运行旧版本的 mySQL (4.0.17)。显然这个版本太旧了，不支持子查询。感谢 Shadow 和 shawnt00，看起来左连接也可以实现同样的效果。这是我当前使用的查询:

SELECT p1.*
FROM products p1
LEFT JOIN products p2
on p1.AccountID=p2.AccountID and p1.Date<p2.Date and p2.Description like "%Apple%"
where p1.Description like "%Apple%" and p2.Date is null and (p1.AccountID=125060 or p1.AccountID=142580 or p1.AccountID=145135 or p1.AccountID=139254);

如果此查询存在任何问题，我会回复。谢谢大家!

Answer 1

在您的查询中，没有任何东西可以保证 mysql 将选择那些具有 max(date) 值的描述字段。实际上，您的版本违反了 mysql 标准，并且只能在某些配置设置下在 mysql 中运行。

解决方案是通过帐户 ID 获取最大日期，其中描述与子查询中的条件匹配，并使用帐户 ID 和最大日期将其连接回表本身:

SELECT p.AccountID, p.Date, p.Description
FROM products p
INNER JOIN (SELECT AccountID, max(Date) as maxdate
FROM products
WHERE Description like "%Apple%" and (AccountID=125060 or AccountID=125687 or AccountID=139554)
GROUP BY AccountID) t
ON p.AccountID=t.AccountID and p.Date=t.maxdate
WHERE Description like "%Apple%";

更新

Mysql v4.0不支持子查询，因此上述方法不适用。您仍然可以使用左连接方法，即自连接产品表并使用 is null 表达式来查找不属于较大日期的日期:

select p1.*
from products p1
left join products p2
on p1.accountid=p2.accountid and p1.date<p2.date
where Description like "%Apple%" and p2.date is null;