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