mysql max无值返回0,MySQL MIN / MAX返回正确的值，但不返回相关记录信息

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

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

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

Example:

"SELECT

CONCAT(work_type, work_id) AS condition_id,

status,

MIN(created_timestamp) as latest

FROM conditions

GROUP BY condition_id"

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

)

)

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

)

)

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.

Any help is appreciated.

Thanks.

解决方案

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

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

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