db2 查询顶部分组依据
我已经尝试了几个小时,但无法获取查询,我想使用 DB2 执行以下操作。从表“公司和用户”中,我有以下每个公司/用户的门票数量信息,
QUERY USING:
SELECT T.USER, COUNT(T.USER) AS QUANITTY, 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
----------------------------------
mark nissn 300
tom toyt 50
steve kryr 80
mark frd 20
tom toyt 120
jose toyt 230
tom nissn 145
steve toyt 10
jose kryr 35
steve frd 100
这应该是结果(每个公司的顶级用户),
user company quantity
----------------------------------
mark nissn 300
jose toyt 230
steve frd 100
steve kryr 80
因为您可以看到公司中有许多用户,每个公司的每个用户都有不同的数量,结果应该 获取每家公司数量最多的用户。即:公司nissn
它有2个用户,每个用户都有(标记为300)和(汤姆标记为145)
,所以它应该给我最高的用户,标记为300。对于<代码>玩具,<代码>frd,<代码>kryr。我在查询中需要所有这些。
我想知道这在查询中是否可能,或者我需要创建一个存储过程。
I've been trying for hours but can't ge the query, i want to do the following using DB2. From table Company and Users I have the following tickets quantity info per company/user
QUERY USING:
SELECT T.USER, COUNT(T.USER) AS QUANITTY, T.COMPANY FROM TICKET T
INNER JOIN COMPANY P ON P.COMPANY = T.COMPANY
GROUP BY (T.USER, T.COMPANY) ORDER BY QUANTITY DESC
Outcome is:
user company quantity
----------------------------------
mark nissn 300
tom toyt 50
steve kryr 80
mark frd 20
tom toyt 120
jose toyt 230
tom nissn 145
steve toyt 10
jose kryr 35
steve frd 100
THIS SHOULD BE THE RESULT(Top user per company)
user company quantity
----------------------------------
mark nissn 300
jose toyt 230
steve frd 100
steve kryr 80
as you can see there are many users in a company and each have different quantities per company, the result should
get the user with the highest quantity per company. i.e. : Company nissn
it has 2 users and each has (mark with 300) and (tom with 145)
so it should give me the highest user which would be mark with 300. The same would be for toyt
, frd
, kryr
. I need all of them in a query.
I wonder if that's possible in a query or I will need to create a stored procedure.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以使用分析查询来完成此操作。但要小心。该模式通常涉及嵌套子查询。 (第一个用于生成数据集,第二个将其添加到模式中,第三个用于选择所需的行。)
在这种情况下,它应该看起来像这样。
原始查询。
分析查询。 (请注意,
s
是为子查询命名的。我没有使用过 DB2,但标准严格不阻止它们被删除,而且我知道至少有一个数据库需要它们。)结果。
组合查询是:
正如我所说,我没有使用过 DB2。但根据 SQL 标准,该查询应该有效。
You can do this with analytic queries. But be careful. The pattern usually works out to involve nested subqueries. (One to produce a dataset, the next to add it to the pattern, the third to select out the rows you want.)
In this case it should look something like this.
Original query.
Analytic query. (Note that the
s
is to name the subquery. I have not used DB2, but the standard strictly doesn't prevent them to be dropped, and I know at least one database that requires them.)Final result.
The combined query is:
As I say I have not used DB2. But according to the SQL standard, that query should work.