MySQL MIN/MAX返回正确的值，但不返回相关的记录信息

本文介绍了MySQL MIN/MAX返回正确的值，但不返回相关的记录信息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我真的很坚持这一点.我显然不了解MIN/MAX概念.

I am really stuck on this. I'm clearly not understanding the MIN/MAX concept.

我正在尝试从work_type和work_id的分组中获取最新行.

I am trying to get the latest row from a grouping of work_type and work_id.

如果我从MIN更改为MAX，它将更改返回的时间戳，但永远不会从该记录中获取状态信息.

If I change from MIN to MAX, it changes the returned timestamp, but it never brings the status info from that record.

示例:

"SELECT CONCAT(work_type, work_id) AS condition_id, status, MIN(created_timestamp) as latest FROM conditions GROUP BY condition_id"

有了MIN，我得到了:

With MIN, I get:

Array ( [0] => Array ( [condition_id] => cutouts00002 [status] => bad [latest] => 2011-02-21 15:20:27 ) [1] => Array ( [condition_id] => paintings00002 [status] => damagez [latest] => 2011-02-21 14:43:35 ) )

有了MAX，我得到了:

With MAX I get:

Array ( [0] => Array ( [condition_id] => cutouts00002 [status] => bad [latest] => 2011-02-21 15:22:20 ) [1] => Array ( [condition_id] => paintings00002 [status] => damagez [latest] => 2011-02-21 14:43:41 ) )

但事实是，具有最新时间戳的行中的状态为无损坏"，但是它从不返回与MAX(current_timestamp)相对应的行，而仅返回"damagez"行.

Bu the thing is that the status in the row with the latest timestamp, is "no damage", but it never returns the row corresponding to the MAX(current_timestamp), it only ever returns the "damagez" row.

感谢您的帮助.

谢谢.

推荐答案

您已成为MySQL宽松规则的牺牲品，该规则允许将非聚合包含在GROUP BY查询中.当然，您使用的是MIN或MAX，一次只能使用 ONE ，但是请考虑以下查询:

You have fallen prey of the MySQL lax rules that allow for non-aggregates to be included in a GROUP BY query. Sure, you are working with MIN or MAX, and only ONE at a time, but consider this query:

SELECT CONCAT(work_type, work_id) AS condition_id, status, MIN(created_timestamp) as earliest, MAX(created_timestamp) as latest FROM conditions GROUP BY condition_id

现在，考虑状态列应该来自哪一行.在聚合(GROUP BY中的那些)和非聚合列之间建立关联是荒谬的.

Now, think about which row the status column should come from. It's absurd to put a correlation between the aggregate (those in the GROUP BY) and non-aggregate columns.

相反，您可以像这样编写查询

Instead, write your query like this

SELECT X.condition_id, C.status, X.earliest FROM ( SELECT CONCAT(work_type, work_id) AS condition_id, status, MIN(created_timestamp) as earliest FROM conditions GROUP BY condition_id ) X JOIN conditions C on CONCAT(c.work_type, c.work_id) = X.condition_id and c.created_timestamp = X.earliest

但是，如果您有两条具有相同created_timestamp的记录，它将变得更加棘手

But if you had two records with the same created_timestamp, it gets even more tricky

SELECT X.condition_id, Max(C.status) status, X.earliest FROM ( SELECT CONCAT(work_type, work_id) AS condition_id, status, MIN(created_timestamp) as earliest FROM conditions GROUP BY condition_id ) X JOIN conditions C on CONCAT(c.work_type, c.work_id) = X.condition_id and c.created_timestamp = X.earliest GROUP BY X.condition_id, X.earliest