每个产品的最大主要号码由下式给出:
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的查询相同。