如果没有关系，则可以使用另一个case表达式：

select (case when score1 not in (least(score1,score2,score3),greatest(score1,score3))
             then 'score1'
             when score1 not in (least(score1,score3))
             then 'score2'
             when score1 not in (least(score1,score3))
             then 'score3'
        end) as second_score

不是可以很好地推广到更多列。

如果这是您需要的，我建议您更改数据模型，以便将数据保存在行中，而不是列中：

id    score    score_number
123      14         1
123     561         2
123     580         3
. . .  

您的问题将更容易回答。

我认为最好的选择就是取消数据表的位置（每个分数只有一行），然后对它们进行排名。如果您希望获得具有原始乐谱的准确输出，则可以再次透视表，或通过text_l.getLayoutParams().height = 50 * 3; text_l.getLayoutParams().width= 100 * 3; 将结果加入。

这是我的建议：

id

结果：

with input(id,score1,score3) as (
    select 123,14,561,580 union all
    select 456,626,771,843 union all
    select 789,844,998,904 union all
    select 111,922,677,301 union all
    select 222,665,578,678 union all
    select 333,416,631,320
),unpivotted(id,score_label,score) as (
         select
             id,case x
                 when 1 then 'score1'
                 when 2 then 'score2'
                 when 3 then 'score3'
                 end,case x
                 when 1 then score1
                 when 2 then score2
                 when 3 then score3
                 end
         from input
                  cross join (select 1 as x union all select 2 union all select 3) as three
     ),ranked as (
         select *,rank() over (partition by id order by score desc) as rnk
         from unpivotted
     )
select distinct
    id,nth_value(score_label,1) over (partition by id order by rnk rows between unbounded preceding and unbounded following ) as high_score_label,2) over (partition by id order by rnk rows between unbounded preceding and unbounded following) as second_score_label,nth_value(score,1) over (partition by id order by rnk rows between unbounded preceding and unbounded following ) as high_score,2) over (partition by id order by rnk rows between unbounded preceding and unbounded following) as second_score
from ranked
order by id