DB2的顶级组 [英] Top Group By DB2

问题描述

我已经尝试了几个小时，但是无法使用DB2来完成我想要的操作。
从表公司和用户我有以下票据数量信息每个公司/用户

 用户公司数量
 ------------ ------------ ------------ 
 mark nissan 300 
 tom丰田50 
史蒂夫克莱斯勒80 
标记ford 20 
汤姆丰田120 
 jose丰田230 
汤姆日产145 
史蒂夫丰田10 
 jose克莱斯勒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 
  

我想看到的是每个公司的最高用户，所以给出上面的数据，查询应该显示：

 用户公司y数量（每个公司的最高用户）
 ------------ ------------ -------------- ------------------ 
标记日产300 
 jose丰田230 
 steve ford 100 
史蒂夫克莱斯勒80 
  

如何编写SQL以返回此结果？

---

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

  SELECT用户，数量，公司
 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 
来自TICKET T JOIN COMPANY P ON P.COMPANY = T.COMPANY 
 GROUP BY（T.USER，T.COMPANY））s）t 
 WHERE r = 1; 
  

解决方案

逐步构建。

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

  SELECT公司，MAX（数量）AS MaxQuantity 
从门票
 GROUP BY公司; 
  

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

  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公司AS P ON P.Company = T.Company 
 GROUP BY（T.User，T.Company）
 ORDER BY QUANTITY DESC 
  

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

  WITH门票AS 
（SELECT T.User，COUNT（T.User）AS Quantity，T.Ccompany 
 FROM Ticket AS T 
 JOIN公司AS P ON P.Company = T.Company 
 GROUP BY（T.User，T.Company）
）
 SELECT T.用户，T.Company，M.MaxQuanti ty 
 FROM Tickets AS T 
 JOIN（SELECT Company，MAX（Quantity）AS MaxQuantity 
 FROM Tickets 
 GROUP BY Company）AS M 
 ON T.Company = M公司与T.Quantity = M.MaxQuantity; 
  

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

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

This is generated by the query:

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

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;

解决方案

Build it up step by step.

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;

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 

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;

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

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