mysql - 一列中的 SQL 最大值

Question

我正在尝试获取在一个特定列(“版本”)中具有最高值的记录。我正在使用 base_id 获取行，并且可能有不止一行具有相同的 base_id，但它们将具有不同的版本号。所以声明的重点是只获取版本最高的那个。下面的语句有效，但前提是实际上有多个值。如果只有一个，我根本得不到任何记录(与预期的一行相反)。那么，即使某些记录只存在一个版本，我如何才能只获得下面版本号最高的值？:

SELECT r.id
     , r.title
     , u.name created_by
     , m.name modified_by
     , r.version
     , r.version_displayname
     , r.informationtype
     , r.filetype
     , r.base_id
     , r.resource_id
     , r.created
     , r.modified
     , GROUP_CONCAT( CONCAT(CAST(c.id as CHAR),',',c.name,',',c.value) separator ';') categories 
  FROM resource r 
  JOIN category_resource cr 
    ON r.id = cr.resource_id 
  JOIN category c 
    ON cr.category_id = c.id 
  JOIN user u 
    ON r.created_by = u.id 
  JOIN user m 
    ON r.modified_by = m.id 
 WHERE r.base_id = 'uuid_033a7198-a213-11e3-93de-2b47e5a489c2' 
   AND r.version = (SELECT MAX(r.version) FROM resource r) 
 GROUP 
    BY r.id;

编辑:

我意识到查询本身的其他部分可能会使事情复杂化，所以我将尝试创建一个更清晰的示例，我希望它应该能显示我所追求的内容。

如果我这样做:

SELECT id, title, MAX(version) AS 'version' FROM resource GROUP BY title

在看起来像这样的 table 上:

然后我得到以下结果:

从表中可以看出，这是不正确的。也就是说，它为每个资源获取了最高值，但是如果您查看简介，例如版本值为 2 的资源的 ID 为 6，而获取的资源的 ID 为 1。所以查询似乎以某种方式组合了来自不同行的值...？

我应该指出，我是 SQL 的新手，我用来举例说明问题的原始查询是我在这里得到的帮助，所以请尽可能清楚地解释，谢谢。

另一个注意事项是我发现了一些关于子查询的建议，但除了没有返回正确的结果之外，它真的很慢。我正在对 5000 行进行测试，我真的希望它只需要几分之一秒才能满足性能要求。

编辑 2:

找到了一种合并声明的方法，有点像建议的方法之一，以及此处的各种解决方案:Retrieving the last record in each group

但是，我尝试了所有这些方法，尽管大多数看起来都有效，但它们非常慢……

拿这个:

SELECT
  r.id, r.title,
  u.name AS 'created_by', m.name AS 'modified_by', r.version, r.version_displayname, r.informationtype,
r.filetype, r.base_id, r.resource_id, r.created, r.modified,
  GROUP_CONCAT( CONCAT(CAST(c.id as CHAR),',',c.name,',',c.value) separator ';') AS 'Categories'
FROM
  resource r
  INNER JOIN
  (SELECT
   DISTINCT r.id AS id
  FROM
   resource r
  INNER JOIN
   category_resource cr1 ON (r.id = cr1.resource_id)
  WHERE
   cr1.category_id IN (9)
) mr

    ON r.id = mr.id
  INNER JOIN category_resource cr
    ON r.id = cr.resource_id
  INNER JOIN category c
    ON cr.category_id = c.id
  INNER JOIN user u
    ON r.created_by = u.id
  INNER JOIN user m
    ON r.modified_by = m.id
INNER JOIN
(
    SELECT max(version) MyVersion, base_id
    FROM resource
    GROUP BY base_id
) r2
  ON r.base_id = r2.base_id
  AND r.version = r2.MyVersion
group by r.base_id
order by r.version desc;

在末尾添加(从 INNER JOIN 开始)以仅获取每个 base_id 具有最高版本值的行，这将查询从 20 毫秒减慢到大约 6-8 秒。那是不行的……但这让我感到惊讶。虽然我显然不是数据库专家，但在我看来应该优化数据库查询以获取这样的数据。但是如果我做我能想到的唯一选择，那就是获取所有记录而不考虑版本号，然后在 PHP 中过滤它们，你猜怎么着？那比这快多了……

我最初认为 PHP 中的过滤对性能造成的影响太大了，但那是大约一秒钟的延迟，所以还是比这好得多。

但我觉得我错过了什么，难道不应该更有效地做到这一点吗？

Answer 1

鉴于您自己的回答，您的问题与您提供的链接中的问题基本相同。由于您有一些子问题，我会尝试在那里为您提供一些额外的帮助。

如果您想在您的数据库中进行某种版本控制，那么您基本上可以通过一些版本列来扩展您的主键。出于您提到的原因，我也会投票赞成使用开始日期/结束日期列。鉴于您自己的答案，您可以相应地修改您的布局。如果可以的话，那是你应该走的路!

在您给出的示例中，不清楚主键是什么，因为“id”列也有变化的值。在您的情况下，主键将是“标题”列。所以你可以使用像

这样的查询

SELECT title, max(version) as version FROM resource GROUP BY title

获得一个结果，您可以在其中看到您的原始主键和最新版本——它们共同构成了您的实际主键。

要获取该表中的所有其他字段，您需要将该结果连接到资源表并将主键字段用作连接条件。

SELECT * FROM (
        SELECT title, max(version) as version 
        FROM resource 
        GROUP BY title) as s 
    INNER JOIN resource r on (r.title = s.title AND r.version = s.version)

为什么您的查询给您错误的结果？

原因是，您的查询中有一个错误，MySQL 已为您“修复”。通常，您需要在 GROUP BY 子句中提供未在聚合函数(如 MAX())中使用的每一列。在你的例子中

SELECT id, title, MAX(version) AS 'version' FROM resource GROUP BY title

您在查询的选择部分中有一个列('id')，您没有在 GROUP BY 子句中提供。

在 MySQL 中，您可以忽略该规则 (see here)。

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

由于“id”列对您的键(“title”列)有不同的值，您只是得到了一些结果——在这种情况下，MySQL 可能只是使用它找到的第一行。但结果本身是未定义的，可能会发生变化，例如当数据库更新或数据增长时。您不应该依赖于从测试时看到的结果中推断出的规则!

在 oracle 和 SQL-Server 等其他数据库上，您会在尝试执行最后一个查询时遇到错误。

我希望我能稍微澄清一下你的结果的原因。