如果分数 > 70分，全部排名第一，其余的按降序排名，一条SQL如何实现

Question

吉他里的俊  15:45:35
name   score
AA     67
BB     85
CC     98
DD     58
EE     72
FF     83
GG     47如果score > 70 , 排名第一, 其余的按降序排名
结果：name   score  rank
AA     67       5
BB     85       1
CC     98       1
DD     58       6
EE     72       1
FF     83       1
GG     47       7

Answer 1

一开始，准备用 rank() over(order by score) as rank
网上搜了半天，没有找到可以在rank()方法里写条件的

Answer 2

比如： rank() over(score > 70 : 1 , order by score desc) as rank
如果有类似于这种写法，如果score >70 排名第一，其余按降序排序，
希望oracle忙出来这种方法，大家想想有没有实现的方法,如果一条sql能实现更好
大家把自己的想法说出来

Answer 3

create table t1 (name varchar2(10),score number(3));insert into t1 values ('AA',67);
insert into t1 values ('BB',85);
insert into t1 values ('CC',98);
insert into t1 values ('DD',58);
insert into t1 values ('EE',72);
insert into t1 values ('FF',83);
insert into t1 values ('GG',47);
commit;select name,
       score,
       case when score >= 70 then 1 else rank() over(order by score desc) end rank
from t1
order by name    name    score    rank
--------------------------------
1 AA 67 5
2 BB 85 1
3 CC 98 1
4 DD 58 6
5 EE 72 1
6 FF 83 1
7 GG 47 7

Answer 4

使用decode判断也可以select name,
       score,
       decode(sign(score-70),-1, rank() over(order by score desc),1) c1
from t1
order by name

Answer 5

HJ_daxian，我用过这种方式，只能满足一种条件，如果条件大于（降序），或者条件小于（升序）时是没问题的，但如果条件是
1.score >=70 and score <=80 排名第一（其余的按升序或者降序）
2.core >=70,其余再按升序排名，
3.core <=70,其余再按降序排名  
这几种条件时就不适合了？

Answer 6

1. 
select name,
       score,
       case when score >= 70 then 1 else rank() over(order by score asc) end rank
from t1
order by name
2.
select name,
       score,
       case when score <= 70 then 1 else rank() over(order by score desc) end rank
from t1
order by name
3.
select name,
       score,
       case when score >= 70 and score <=80 then 1 else rank() over(order by score desc) end rank
from t1
order by name
如果这三种情况时，有没有什么好办法

Answer 7

呵呵，这是客户的要求，我这只是个例子，其实现实中并不是分数的统计，其实更复杂，这个sql只是我整个sql语句中的一小部分，也是核心部分，大家帮忙想想办法

Answer 8

SELECT t.*,
       RANK() OVER(ORDER BY CASE WHEN t.score >= 70 AND t.score <= 85 THEN NULL ELSE t.score END DESC)
  FROM tb t

Answer 9

hudingchen，因为这些都是客户自己设置的，比如：
1。值范围（值>？ and 值<？）
2。大于值或小于值（a.值>? b.值<?)
3.排序方式：asc或desc
这些参数都是客户自己随意设置的
如果上面的排序方式改成asc,就会有问题

Answer 10

用动态SQL不就得了，想怎么拼就怎么拼SQL

Answer 11

create table t1 (name varchar2(10),score number(3));insert into t1 values ('AA',67);
insert into t1 values ('BB',85);
insert into t1 values ('CC',98);
insert into t1 values ('DD',58);
insert into t1 values ('EE',72);
insert into t1 values ('FF',83);
insert into t1 values ('GG',47);
commit;--1.  
select name,
  score,
  case when score >= 70 then 1 else rank() over(order by score asc) end rank
from t1
order by rank;
结果：
/*
1 GG 47 1
2 EE 72 1
3 BB 85 1
4 CC 98 1
5 FF 83 1
6 DD 58 2
7 AA 67 3
*/
--分析错误：GG应该排第2(这里排在了第一）,DD排第3，AA排第四
--2.
select name,
  score,
  case when score <= 70 then 1 else rank() over(order by score desc) end rank
from t1
order by rank;
/*
1 CC 98 1
2 GG 47 1
3 AA 67 1
4 DD 58 1
5 BB 85 2
6 FF 83 3
7 EE 72 4
--分析错误：CC应该排第2(这里排在了第一）,BB排第3，FF排第4，EE排第5
*/
--3.
select name,
  score,
  case when score >= 70 and score <=80 then 1 else rank() over(order by score desc) end rank
from t1
order by name;
/*
1 AA 67 5
2 BB 85 2
3 CC 98 1
4 DD 58 6
5 EE 72 1
6 FF 83 3
7 GG 47 7
*/
--分析错误：CC应该排第2，以此类推总结：所有在条件之内的都排第一，然后再按照设定的asc或者desc对剩下的进行排序，排序从应该2开始

Answer 12

满足条件的全部排名第一，剩下的排名从2开始（asc或者desc要看客户的设置）

Answer 13

大家启发了我的思路，如下解决方案
--1.   
select name,
  score,
  case when score >= 70 then 1 else rank() over(order by score asc) end rank
from t1
order by rank;
改为
select name,
  score,
  case when score >= 70 then 1 else rank() over(order by score asc) + 1 end rank
from t1
order by rank;--2.
select name,
  score,
  case when score <= 70 then 1 else rank() over(order by score desc) end rank
from t1
order by rank;
改为
select name,
  score,
  case when score <= 70 then 1 else rank() over(order by score desc) + 1 end rank
from t1
order by rank;
--3.
select name,
  score,
  case when score >= 70 and score <=80 then 1 else rank() over(order by score desc) end rank
from t1
order by name;
改为
select name,
  score,
  case when score >= 70 and score <=80 then 1 else rank() over(order by score desc) + 1 end rank
from t1
order by name;
总结：
满足条件的全部排名为 1 ，剩下的排名还是正常排名从1开始(但要在后面再加1)
不管客户如何设置，这种做法都满足条件