I have a query in SQL looks like that:
我的SQL查询是这样的:
select fldCustomer, fldTerminal, COUNT(fldbill)
from tblDataBills
group by fldCustomer, fldTerminal
order by fldCustomer
results looks like:
结果看起来像:
fldCustomer fldTerminal (number of bills)
0 1 19086
0 2 10
0 5 236
1 1 472
1 5 3
1 500 19
2 1 292
2 500 22
how can i get the MAX count of each customer so i get results like
如何才能得到每个客户的最大计数,从而得到像这样的结果
0 1 19086
1 1 472
2 1 292
Thanks in advance!
提前谢谢!
2 个解决方案
#1
2
Use a subquery with row_number()
:
使用带有row_number()的子查询:
select fldCustomer, fldTerminal, cnt
from (select fldCustomer, fldTerminal, COUNT(*) as cnt,
row_number() over (partition by fldCustomer order by count(*) desc) as seqnum
from tblDataBills
group by fldCustomer, fldTerminal
) db
where seqnum = 1
order by fldCustomer ;
Note that in the event of ties, this will arbitrarily return one of the rows. If you want all of them, then use rank()
or dense_rank()
.
注意,在连接事件中,这将会任意地返回其中的一行。如果您想要全部,那么使用rank()或dense_rank()。
#2
1
This might require a little trickery with the RANK()
function
这可能需要使用RANK()函数进行一些技巧
SELECT fldCustomer, fldTerminal, [(number of bills)] FROM ( SELECT fldCustomer, fldTerminal, COUNT(fldbill) [(number of bills)], RANK() OVER (PARTITION BY fldCustomer ORDER BY COUNT(fldbill) DESC) Ranking FROM tblDataBills GROUP BY fldCustomer, fldTerminal ) a WHERE Ranking = 1
#1
2
Use a subquery with row_number()
:
使用带有row_number()的子查询:
select fldCustomer, fldTerminal, cnt
from (select fldCustomer, fldTerminal, COUNT(*) as cnt,
row_number() over (partition by fldCustomer order by count(*) desc) as seqnum
from tblDataBills
group by fldCustomer, fldTerminal
) db
where seqnum = 1
order by fldCustomer ;
Note that in the event of ties, this will arbitrarily return one of the rows. If you want all of them, then use rank()
or dense_rank()
.
注意,在连接事件中,这将会任意地返回其中的一行。如果您想要全部,那么使用rank()或dense_rank()。
#2
1
This might require a little trickery with the RANK()
function
这可能需要使用RANK()函数进行一些技巧
SELECT fldCustomer, fldTerminal, [(number of bills)] FROM ( SELECT fldCustomer, fldTerminal, COUNT(fldbill) [(number of bills)], RANK() OVER (PARTITION BY fldCustomer ORDER BY COUNT(fldbill) DESC) Ranking FROM tblDataBills GROUP BY fldCustomer, fldTerminal ) a WHERE Ranking = 1