如何在SQL中的多列中获得第二高的值

问题描述

我有一些像下面这样的数据

I have some data like below

    Name     Flag1   Flag2  Flag3  Flag4
    Jason    500     400    200    100
    Mark     400     299    250    183
    Tom      932     331    283    844

我能够通过编写

Max = CASE WHEN flag1>flag2 AND flag1>flag3 AND flag1>flag4 THEN 'flag1' 
           WHEN flag1<flag2 AND flag2>flag3 AND flag2>flag4 THEN 'flag2'
           WHEN flag1<flag3 AND flag2<flag3 AND flag3>flag4 THEN 'flag3'
           WHEN flag1<flag4 AND flag2<flag4 AND flag3<flag4 THEN 'flag4'END

您能帮我找到 Flag1-Flag4 中的第二大值吗?例如，Jason 的第二大应该是 flag2

Could you please help me with finding the second largest value within Flag1-Flag4? For example, the 2nd largest for Jason should be flag2

先谢谢你！

推荐答案

您可以对数据进行逆透视，然后进行如下查询

You can unpivot your data then make the query as below

SELECT name,flags,flag FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY flag DESC) rn,*
    FROM
    (
        SELECT name, flag, flags
        FROM
        (
          SELECT 'json' name, 500 flag1,400  flag2,200 flag3, 100 flag4
          UNION ALL
          SELECT 'Mark' name, 400 flag1,299  flag2,250 flag3, 183 flag4
          UNION ALL
          SELECT 'Tom' name, 932 flag1,331  flag2,283 flag3, 844 flag4
        ) AS cp
        UNPIVOT 
        (
          flag FOR flags IN (flag1, flag2, flag3, flag4)
        ) AS up
    )x
)y
WHERE rn=2

附言在查询的中间，我只是用一堆选择和联合来模拟你的数据

p.s. In the middle of the query I just simulate your data with bunch of selects and union all

p,s 你也可以通过这个查询得到第一个金额，看rn = 2

p,s Also you can get the first amount by this query too, look at rn = 2