问题描述
我已经尝试了几个小时，但是无法使用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的顶级组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！