我写的sql语句如下
select name,score,row_number() over (oder by score desc) as sequence from tb_score oder by score desc这样查出3列数据
name | score | sequence
xxx 97 1
xxx 95 2
xxx 93 3
xxx 90 4
xxx 88 5
xxx 86 6
xxx 84 7
xxx 83 8
xxx 81 9
xxx 80 10
现在上面有新的要求 = = 还要按照比例分 RANK,比如排名前20%的为A ,前30%的为B,前40%的为C,前10%的为D就要如下的结果
name | score | sequence rank
xxx 97 1 A
xxx 95 2 A
xxx 93 3 B
xxx 90 4 B
xxx 88 5 B
xxx 86 6 C
xxx 84 7 C
xxx 83 8 C
xxx 81 9 C
xxx 80 10 D
敢问各位大大SQL语句如何改写,或者如何实现上述查询结果。PS:我是用jsp开发的,这4个比例最好是变量,比例可能根据实际情况调整,我想在后台做个管理界面调节4个比例变量,否则每次变更比例改程序的话对维护工作方面比较麻烦。
select name,score,row_number() over (oder by score desc) as sequence from tb_score oder by score desc这样查出3列数据
name | score | sequence
xxx 97 1
xxx 95 2
xxx 93 3
xxx 90 4
xxx 88 5
xxx 86 6
xxx 84 7
xxx 83 8
xxx 81 9
xxx 80 10
现在上面有新的要求 = = 还要按照比例分 RANK,比如排名前20%的为A ,前30%的为B,前40%的为C,前10%的为D就要如下的结果
name | score | sequence rank
xxx 97 1 A
xxx 95 2 A
xxx 93 3 B
xxx 90 4 B
xxx 88 5 B
xxx 86 6 C
xxx 84 7 C
xxx 83 8 C
xxx 81 9 C
xxx 80 10 D
敢问各位大大SQL语句如何改写,或者如何实现上述查询结果。PS:我是用jsp开发的,这4个比例最好是变量,比例可能根据实际情况调整,我想在后台做个管理界面调节4个比例变量,否则每次变更比例改程序的话对维护工作方面比较麻烦。
select name,score,decode(sequence,1,'A',2,'B',3,'C','D') from(
select name,score,ntile(4) over (oder by score desc) as sequence from tb_score )
order by score desc
select name,score,ntile(4) over (oder by score desc) as sequence from tb_score )
order by score desc
2 xxx 95 A
3 xxx 93 A
4 xxx 90 B
5 xxx 88 B
6 xxx 86 B
7 xxx 84 C
8 xxx 83 C
9 xxx 81 D
10 xxx 80 D
(
select 'xxx' a,97 b,1 c from dual
union all
select 'xxx',95,2from dual
union all
select 'xxx',93,3from dual
union all
select 'xxx',90,4from dual
union all
select 'xxx',88,5from dual
union all
select 'xxx',86,6from dual
union all
select 'xxx',84,7from dual
union all
select 'xxx',83,8from dual
union all
select 'xxx',81,9 from dual
union all
select 'xxx',80,10 from dual
union all
select 'xxx',79,11 from dual
union all
select 'xxx',78,12 from dual
)
select a.*,c/rn,case when c<rn*0.3 then 'A'
when c<=rn*0.5 and c>=rn*0.3 then 'B'
when c<=rn*0.9 and c>rn*0.5 then 'C'
else 'D' end
from
(
select t.*,count(1) over(partition by 1 order by 1) rn
from temp t
group by a,b,c
)a
select name,score,
case when sequence<3 then 'A' when sequence<6 then 'B' when <10 then 'C' else 'D' end from(
select name,score,ntile(10) over (oder by score desc) as sequence from tb_score )
order by score desc
1 xxx 80 10 10 1 D
2 xxx 81 9 10 0.9 C
3 xxx 83 8 10 0.8 C
4 xxx 84 7 10 0.7 C
5 xxx 86 6 10 0.6 C
6 xxx 88 5 10 0.5 B
7 xxx 90 4 10 0.4 B
8 xxx 93 3 10 0.3 B
9 xxx 95 2 10 0.2 A
10 xxx 97 1 10 0.1 A
总数未必一定就是10~cosiod 在5楼的回复 这样是用到临时表?但是临时表里也不应该手动指定啊 我的数据只是个形式,未必一定是10行啊。。
你可以增加记录数试试
不是很明白你的意思..
如果15%为a的话,可以100等分,然后case里a条件部分取值用<=15
也可以分成20等分,取<=3
等分数不要超过记录条数
我想用cume_dist函数比较好
select name,score,
case when sequence<=0.2 then 'A' when sequence<=0.5 then 'B' when sequence<=0.9 then 'C' else 'D' end from(
select name,score,cume_dist() over (order by score desc) as sequence from tb_score )
order by score desc