mysql - 根据 2 个条件从多个 MySQL 表中获取最新记录

Question

我有一个数据库，其中包含一些服务表，这些服务表包含有关我们已部署到各种环境中的包的信息。然后查询该数据库以提供已安装软件的最新版本的总体情况，并显示在网页的表格中。

我正在努力做的事情是获取每个环境中安装的每个包的最新版本。我开始使用这篇文章中的信息:

Retrieving the last record in each group

这对软件的另一个方面有很大帮助，但我失败的地方是因为我有多个选择标准。数据库相关部分如下

包版本表

package_versions

id
server_name_id
package_name_id
version

包名称表

package_names

id
name

服务器名称表

server_names

id
name
environment_name_id

最后是环境名称表

environment_names

id
name

这种结构使我能够保存包的历史记录以及部署到各种环境中的版本。

现在我需要做的是获取部署在每个环境中的所有包的最新版本。正如我所说，当我玩一张 table 和两个标准时，我已经成功地解决了上面的帖子，但这里是 4 个 table 和 2 个标准。

我的尝试要么返回环境中已安装的每个版本:

SELECT  environment_names.name as environment,
        package_names.display as package,
        package_names.name as pkg_name,
        package_versions.version as package_version
FROM    package_versions INNER JOIN package_names ON package_names.id = package_versions.package_name_id
                         INNER JOIN server_names ON server_names.id = package_versions.server_name_id
                         INNER JOIN environment_names ON environment_names.id = server_names.environment_name_id
                         LEFT JOIN package_versions pv ON (package_versions.package_name_id = pv.package_name_id 
                                                           AND package_versions.id < pv.id)
AND     pv.id IS NULL
ORDER BY environment_names.name, package_names.name

不可能只在版本号上使用 Max，因为它是由 Maven 生成的，所以它采用 x.x.x 格式并且可以包含 SNAPSHOT。

其他尝试只是为我提供安装在任何地方的最新版本。

我想我需要在 LEFT join 部分添加一些东西，但我真的很难弄清楚是什么。非常感谢您的帮助。

非常感谢，

罗素

Answer 1

你需要的是MySQL目前没有提供的排序功能。有一种方法可以模拟它。此外，您需要拆分版本值，以便对它们进行排序。

Select envName, pkgName, version
From (
      Select envName, pkgName, version
        , @rnk := if(@env=envId && @pkg=package_name_id, if(@pkgVer=verId, @rnk, @rnk + 1), 1) As rnk
        , @pkgVer := verId
        , @env := envId
        , @pkg := package_name_id
      From (
            Select env.id As envId
              , env.name As envName
              , ver.package_name_id
              , pkg.name As pkgName
              , ver.id As verId
              , ver.version
              , Cast(Substring_Index( ver.version , '.', 1 ) As signed) As Major
              , Cast(Substring_Index(Substring_Index( ver.version , '.', 2 ),'.',-1) As signed) As Minor
              , Cast(Substring_Index(Substring_Index( ver.version , '.', -2 ),'.',1) As signed) As Build
              , Cast(Substring_Index( ver.version , '.', -1 ) As signed) As Revision
            From package_versions As ver
              Join package_names As pkg
                On pkg.id = ver.package_name_id
              Join server_names As sys
                  On sys.id = ver.server_name_id
              Join environment_names As env
                  On env.id = sys.environment_name_id
              Cross Join ( Select @rnk := 0
                              , @ver := ''
                              , @env := 0
                              , @pkg := 0 ) As Z
          ) As envPackages
      Order By envName, pkgName, Major Desc, Minor Desc, Build Desc, Revision Desc
      ) As rnkedPackages
Where rnk = 1
Order By envName, pkgName, rnk

SQL Fiddle