我一直在尝试使用其他帖子的建议来查找组中每个成员的最大值,但是似乎这是一个不同的问题,因为计数器基于count(*)而不是特定列上的计数。
我的表有几列;我需要的是:日期和分支。该表的每个记录代表该分支中的一个事务。我需要知道每个日期是哪个分支有更多交易以及完成了多少笔交易。
我从开始:
Select date, branch, count(*) as total
from table
group by date, branch
我尝试了一个max(total),但这只会给我一行,而不是每组一个。
我尝试加入自己,类似这样,但是它不起作用,因为在having子句中无法识别格言:
Select date, branch, count(*) as maxim
(Select date, branch, count(*) as total
from table
group by date, branch) a
having maxim=max(total)
group by date, branch
有什么想法吗,谢谢!
试试这个:
select t1.date, t2.branch, t1.max_total
from (
select date, max(total) as max_total
from (
select date, branch, count(*) as total
from mytable
group by date, branch) as x
group by date
) as t1
join (
select date, branch, count(*) as total
from mytable
group by date, branch
) as t2 on t1.date = t2.date and t1.max_total = t2.total
这个想法是使用您以两次开始的查询作为派生表:
date
branch
具有值total
数等于最大数量。如果有联系,则可能有多个分支。如果DB2支持窗口功能,则可以使用以下方法,如果适用,则可以更有效:
select date, branch, total
from (
select date, branch, count(*) as total,
rank() over (partition by date order by count(*) desc) as rn
from mytable
group by date, branch) as t
where t.rn = 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句