MySQL MIN/MAX返回正确的值,但不返回相关的记录信息 [英] MySQL MIN/MAX returning proper value, but not the related record info
问题描述
我真的很坚持这一点.我显然不了解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
这篇关于MySQL MIN/MAX返回正确的值,但不返回相关的记录信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!