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;
    
2020-02-25 3 条评论
分享

没有找到相关结果

    已邀请:

    视蕉梁拌客

            逐步建立它。 假设问题中显示的第一个数据表称为\'Tickets \'，请找到每个公司的最大数量： 
    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子查询。     
    2020-02-25 0 0
    分享

    曝匿弄罚

            这应该工作。创建一个派生视图以计算每个用户和每个公司的数量。然后获得数量的最大值，然后将最大值加入到数量的计算中。 
    SELECT p.company, 
       t.user, 
       t.quantity 
FROM   (SELECT MAX(t.quantity) max_quantity, 
               t.company 
        FROM   (SELECT  
                       COUNT(t.user) quantity, 
                       t.company 
                FROM   ticket t 
                GROUP  BY t.company) t) maxq 
       INNER JOIN (SELECT t.user, 
                          t.company, 
                          COUNT(t.user) quantity 
                   FROM   ticket t 
                   GROUP  BY t.company, 
                             t.user) t 
         ON maxq.max_quantity = t.quantity 
            AND maxq.company = t.company 
       INNER JOIN company p 
         ON p.company = t.company 
ORDER  BY t.quantity DESC
    在此处可以找到一个工作示例，该示例按标签显示StackOverflow数据的最高用户。     
    2020-02-25 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.039133071899414