通过下列SQL取得了一组数据
select count(*) as count, fileName as name from table where type = 'a01' group by fileName order by count desccount name
25 jk
23 wtt
19 f9
17 wy
13 jqq
10 alk现在我需要加一个排名,期望能得到下面的效果
count name rank
25 jk 1
23 wtt 2
19 f9 3
17 wy 4
13 jqq 5
10 alk 6求教sql的写法。谢谢!
select count(*) as count, fileName as name from table where type = 'a01' group by fileName order by count desccount name
25 jk
23 wtt
19 f9
17 wy
13 jqq
10 alk现在我需要加一个排名,期望能得到下面的效果
count name rank
25 jk 1
23 wtt 2
19 f9 3
17 wy 4
13 jqq 5
10 alk 6求教sql的写法。谢谢!
DENSE_RANK() 相同排名不跳过。select count(*) as count,
fileName as name,
rank over(order by count(*)) as rank
from table
where type = 'a01'
group by fileName
order by count desc
select count(*) as count,
fileName as name,
dense_rank over(order by count(*)) as dense_rank
from table
where type = 'a01'
group by fileName
order by count desc
直接在你的查询里面加个字段,rownum 试试
楼上的只是排序,假如count相同也会排序,不会出现相同排名。
谢谢1楼,2楼,3楼。 你这条可以的。想再问一下,现在的排名是相同的count量不同的名次,如果我想相同的count量是一样的名次,该如何呢?
现在:
count name rank
11 ab 3
11 jj 4
10 alk 5期望:
count name rank
11 ab 3
11 jj 3
10 alk 4
...楼主可以认真看看rank,dense_rank这两个分析函数。
DENSE_RANK:11 12 12 13 中式