如何根据多行中的最大值选择单行

一尘不染

如何根据多行中的最大值选择单行

sql

我有一个包含四列的表：

name   major    minor  revision
p1     0        4      3
p1     1        0      0
p1     1        1      4
p2     1        1      1
p2     2        5      0
p3     3        4      4

这基本上是一个ca表，其中包含程序每个版本的记录。我想进行选择以获取所有程序及其最新版本，因此结果将如下所示：

name   major    minor  revision
p1     1        1      4
p2     2        5      0
p3     3        4      4

我不能仅按名称分组并获得每一列的最大值，因为那样我最终只会得到每一列的最高编号，而不是具有最高版本的特定行。我该如何设置？

阅读 202

2021-03-17

共1个答案

一尘不染

我尝试解决SQL问题的方法是逐步解决问题。

您需要与每个产品的最大主要版本相对应的最大次要版本的最大修订版。

每个产品的最大主要号码由下式给出：

SELECT Name, MAX(major) AS Major FROM CA GROUP BY Name;

因此，与每个产品的最大主号码相对应的最大次号码为：

SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
  FROM CA
  JOIN (SELECT Name, MAX(Major) AS Major
          FROM CA
         GROUP BY Name
       ) AS CB
    ON CA.Name = CB.Name AND CA.Major = CB.Major
 GROUP BY CA.Name, CA.Major;

因此，最大修订版（与每个产品的最大主要版本相对应的最大次要版本号）由下式给出：

SELECT CA.Name, CA.Major, CA.Minor, MAX(CA.Revision) AS Revision
  FROM CA
  JOIN (SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
          FROM CA
          JOIN (SELECT Name, MAX(Major) AS Major
                  FROM CA
                 GROUP BY Name
               ) AS CB
            ON CA.Name = CB.Name AND CA.Major = CB.Major
         GROUP BY CA.Name, CA.Major
       ) AS CC
    ON CA.Name = CC.Name AND CA.Major = CC.Major AND CA.Minor = CC.Minor
 GROUP BY CA.Name, CA.Major, CA.Minor;

经过测试-
它的工作原理和产生的答案与Andomar的查询相同。

表现

我创建了大量数据（11616行数据），并对Andomar针对我的查询进行了基准测试-目标DBMS是在MacOS X 10.7.2上运行的IBMInformix DynamicServer（IDS）版本11.70.FC2。我使用了Andomar的两个查询中的第一个，因为IDS在第二个查询中不支持比较表示法。我加载了数据，更新了统计数据，并依次运行了我的查询和Andomar的查询，并依次运行了Andomar的查询和我的查询。我还记录了IDS优化器报告的基本费用。来自两个查询的结果数据是相同的（因此，查询都是准确的-或同样不准确）。

表未索引：

Andomar's query                           Jonathan's query
Time: 22.074129                           Time: 0.085803
Estimated Cost: 2468070                   Estimated Cost: 22673
Estimated # of Rows Returned: 5808        Estimated # of Rows Returned: 132
Temporary Files Required For: Order By    Temporary Files Required For: Group By

具有唯一索引的表（名称，主要，次要，修订）：

Andomar's query                           Jonathan's query
Time: 0.768309                            Time: 0.060380
Estimated Cost: 31754                     Estimated Cost: 2329
Estimated # of Rows Returned: 5808        Estimated # of Rows Returned: 139
                                          Temporary Files Required For: Group By

如您所见，该索引极大地提高了Andomar查询的性能，但在该系统上它似乎仍然比我的查询贵。该索引为我的查询节省了25％的时间。我很好奇，看到有和没有索引的可比较数据量下，两个版本的Andomar查询的可比数据。（如果需要，可以提供我的测试数据；共有132个产品-
问题中列出的3个产品和129个新产品；每个新产品都有（相同）90个版本条目。）

出现差异的原因是，Andomar查询中的子查询是相关子查询，这是一个相对昂贵的过程（在缺少索引的情况下，这是非常昂贵的）。

2021-03-17