简单举例
select name, score1, score2, nextscore1,
case
when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
then score2
else score1
end score_sort1,
case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1)
then score1
else score2
end score_sort2
from
(select
name,
score1,
score2,
LEAD(score1, 1) over (order by score1 desc, score2 desc) as nextscore1
from
scores
group by
name,
score1,
score2)
ORDER BY
case
when ((nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1))
then score2
else score1
end ,
case when (nextscore1 IS NOT NULL) AND (score1 - nextscore1 < 0.1)
then score1
else score2
end ;