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

Question

我真的很困惑。我显然不理解最小/最大概念。

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

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

示例：

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

使用 MIN，我得到：

    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，我得到：

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 问题是具有最新时间戳的行中的状态是“无损坏”，但它永远不会返回与 MAX(current_timestamp) 对应的行，它只返回曾经返回“damagez”行。

任何帮助表示赞赏。

谢谢。

Answer 1

您已经成为 MySQL 宽松规则的牺牲品，这些规则允许在 GROUP BY 查询中包含非聚合。当然，您正在使用 MIN 或 MAX，并且一次仅一个，但请考虑以下查询：

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 中的列）和非聚合列之间建立关联是荒谬的。

相反，像这样编写查询

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的记录，它会变得更加棘手

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

Answer 2

为此我通常不得不做一些非常令人讨厌的事情。

我的解决方案是T-SQL，但我希望你能重做它。

SELECT 
    CONCAT(c.work_type, c.work_id) as condition_id, 
    c.status, 
    c.created_timestamp as latest
FROM conditions c
JOIN (SELECT work_type, work_id, max(current_timestamp) as latest GROUP BY work_type, work_id) c2 
    ON c.work_type = c2.work_type
    AND c.work_id = c2.work_id
    AND c.created_timestampe = c2.latest

Answer 3

我更喜欢将子查询放在 WHERE 子句中，因为这样读起来更容易。

SELECT 
    CONCAT(work_type, work_id) AS condition_id, 
    status,
    created_timestamp as latest
FROM
    conditions
WHERE
    created_timestamp = (
        SELECT
            MIN(conditions2.created_timestamp)
        FROM
            conditions AS conditions2
        WHERE
            conditions2.condition_id = conditions.condition_id
    )
GROUP BY
    condition_id

此外，如果第一个查询有任何附加的联接或 where 子句，则可以在子查询中重复它们。

SELECT 
    CONCAT(work_type, work_id) AS condition_id, 
    status,
    created_timestamp as latest
FROM
    conditions
    INNER JOIN some_table on condition.id = some_table.condition_id
WHERE
    some_table.some_column > 50 AND 
    created_timestamp = (
        SELECT
            MIN(conditions2.created_timestamp)
        FROM
            conditions AS conditions2
            INNER JOIN some_table AS some_table2 on condition2.id = some_table2.condition_id
        WHERE
            some_table.some_column > 50 AND 
            conditions2.condition_id = conditions.condition_id
    )
GROUP BY
    condition_id