mysql - 跨表 MySQL 查找多个组中最好的

Question

我有一个名为 Packages 的表，其中包含一些数据和一个包含其他信息的相关表。设置如下:

Name|Version|Architecture|Date|id

在第二个名为“分布”的表中，我有以下内容:

repo|id

ID 相互引用。我希望能够获得特定分组的最新(按日期排序)。我想按名称、架构和存储库进行分组。问题是我似乎无法让正常的组中最大的 n 组正常工作，因为我必须遍历 table 。这是我到目前为止所拥有的

select
    p1.Name,
    p1.Version,
    p1.Arch,
    d1.repo,
    p1.Date
from Packages as p1
inner join Distribution as d1
    on p1.id = d1.id
inner join (
    select
        Name,
        Arch,
        repo,
        max(Date) as Date
    from Packages
    inner join Distribution
        on Packages.id = Distribution.id
    group by
        Name,
        Arch,
        repo
) as sq
    on p1.Name = sq.Name and p1.Arch = sq.Arch and d1.repo = sq.repo and p1.Date = sq.Date

问题在于它似乎没有正确对存储库进行分组。它没有给我一个独特的存储库。

编辑:我想获取每个存储库的最新版本。所以我必须获取每个存储库中的最大日期，然后进行分组。

编辑2:我用一个答案做了一点工作来得到这个:

select p1.Name, p1.Version, p1.Arch, d1.repo, p1.Date
from Packages as p1 inner join
    Distribution as d1
    on p1.id = d1.id inner join (
        select Name, Arch, repo, max(Date) as Date
        from Packages inner join Distribution
            on Packages.id = Distribution.id
        group by Name, Arch, repo
    ) as sq
    on p1.Name = sq.Name and p1.Arch = sq.Arch and d1.repo = sq.repo and p1.Date = sq.Date

这似乎对我有用。

Answer 1

我认为您想从最大查询中删除repo:

select p1.Name, p1.Version, p1.Arch, d1.repo, p1.Date
from Packages p1 inner join
     Distribution d1
     on p1.id = d1.id inner join
     (select Name, Arch, max(Date) as Date
      from Packages
      group by Name, Arch
     ) sq
     on p1.Name = sq.Name and p1.Arch = sq.Arch and p1.Date = sq.Date;

编辑:

如果并非所有存储库都有日期，那么您需要按子查询中的分布进行过滤。但是，您仍然不想将 repo 放入聚合中:

select p1.Name, p1.Version, p1.Arch, d1.repo, p1.Date
from Packages p1 inner join
     Distribution d1
     on p1.id = d1.id inner join
     (select Name, Arch, max(Date) as Date
      from Packages p join
           Distribution d
           on p.id = d.id
      group by Name, Arch
     ) sq
     on p1.Name = sq.Name and p1.Arch = sq.Arch and p1.Date = sq.Date;