按 DB2 排列的顶级组 [英] Top Group By DB2

问题描述

我已经尝试了好几个小时，但无法让查询使用 DB2 来做我想做的事情.从表公司和用户我有以下每个公司/用户的门票数量信息

I've been trying for hours but can't get the query to do what I want using DB2. From table Company and Users I have the following tickets quantity info per company/user

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

我想看到的是每家公司的顶级用户，所以根据上面的数据，查询应该显示我:

What I want to see is the top user for each company, so given the data above, the query should show me:

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

如何编写 SQL 来返回此结果?

How can I write the SQL to return this result?

最终答案(在评论中注明):

Final answer (noted in a comment):

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;

推荐答案

一步一步搭建.

假设问题中显示的第一个数据表名为Tickets"，求每家公司的最大数量:

Find the maximum quantity for each company, assuming the first data table shown in the question is called 'Tickets':

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

现在，找到该公司最大数量的用户的数据:

Now, find the data for the user(s) with that maximum quantity for that 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 分，那么这个查询会列出这两个用户.

If the top quantity for a particular company was, say, 200 and two users both scored 200 for that company, then this query lists both users.

现在，如果您的意思是您在问题中显示的查询生成了第一个结果表，那么我刚才所说的票证需要是派生表:

Now, if you mean that the query you show in the question generates the first result table, then what I called tickets just above needs to be the derived table:

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 标准它是正确的):

In which case, we can use a WITH clause (syntax unchecked, but I think it is correct per SQL standard):

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 子查询写两次.

Clearly, you can also write the WITH sub-query out twice if you prefer.

这篇关于按 DB2 排列的顶级组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！