按 DB2 划分的顶级组

我已经尝试了几个小时，但无法让查询使用 DB2 执行我想要的操作。 从表“公司和用户”中，我有每个公司/用户的以下门票数量信息

user         company      quantity
------------ ------------ ------------
mark         nissan       300
tom          toyota       50
steve        krysler      80
mark         ford         20
tom          toyota       120
jose         toyota       230
tom          nissan       145
steve        toyota       10
jose         krysler      35
steve        ford         100

这是由查询生成的：

SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY FROM TICKET T
INNER JOIN COMPANY P ON P.COMPANY = T.COMPANY 
GROUP BY (T.USER, T.COMPANY) -- ORDER BY QUANTITY DESC

我想看到的是每个公司的顶级用户，因此根据上面的数据，查询应该向我显示：

user         company      quantity (Top user per company)
------------ ------------ --------------------------------
mark         nissan       300
jose         toyota       230
steve        ford         100
steve        krysler      80

我该如何编写 SQL 来返回这个结果？

最终答案（在评论中注明）：

SELECT user, quantity, company
  FROM (SELECT user, quantity, company,
               RANK () OVER (PARTITION BY company ORDER BY quantity DESC) AS r
          FROM (SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY
                  FROM TICKET T JOIN COMPANY P ON P.COMPANY = T.COMPANY
                 GROUP BY (T.USER, T.COMPANY) ) s ) t
 WHERE r = 1;

一步步把它建立起来。

假设问题中显示的第一个数据表称为“门票”，请查找每个公司的最大数量：

SELECT Company, MAX(Quantity) AS MaxQuantity
  FROM Tickets
 GROUP BY Company;

现在，查找该公司具有最大数量的用户的数据：

SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

例如，如果特定公司的最高数量为 200，并且该公司的两个用户的得分均为 200，则此查询会列出这两个用户。

现在，如果您的意思是问题中显示的查询生成第一个结果表，那么我上面所说的门票需要是派生表：

SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
  FROM Ticket AS T
 INNER JOIN Company AS P ON P.Company = T.Company 
 GROUP BY (T.User, T.Company)
 ORDER BY QUANTITY DESC 

在这种情况下，我们可以使用WITH子句（语法未经检查，但我认为根据SQL标准它是正确的）：

WITH Tickets AS
    (SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
       FROM Ticket AS T
       JOIN Company AS P ON P.Company = T.Company 
      GROUP BY (T.User, T.Company)
    )
SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

显然，如果您愿意，也可以将WITH 子查询写出两次。