如何對另一列中的列結果進行排序(SQLServer)

我有這張桌子

RowCnt  Lvl a_TargetID  b_TargetID a_LOG a_RF a_GNB a_SVC  a_1st  a_2nd  a_3rd  a_4th
1000    0   100         102        95    83   98    30     NULL   NULL   NULL   NULL
1000    0   100         103        67    84   28    99     NULL   NULL   NULL   NULL
1000    0   100         104        74    88   94    57     NULL   NULL   NULL   NULL
1500    3   1           33         75    26   93    85     NULL   NULL   NULL   NULL
1000    0   100         34         98    55   34    33     NULL   NULL   NULL   NULL
1000    0   100         35         47    45   45    46     NULL   NULL   NULL   NULL

我表中的關鍵列是 RowCnt , Lvl , a_TargetID , b_TargetID

我想將列的名稱作為它們在 a_1st a_2nd a_3rd a_4th

所以最終的結果會是這樣

RowCnt  Lvl a_TargetID  b_TargetID a_LOG a_RF a_GNB a_SVC  a_1st  a_2nd  a_3rd  a_4th
1000    0   100         102        95    83   98    30     a_GNB   a_LOG   a_RF   a_SVC
1000    0   100         103        67    84   28    99     a_SVC   a_RF   a_LOG   a_GNB
1000    0   100         104        74    88   94    57     a_GNB   a_RF   a_LOG   a_SVC
1500    3   1           33         75    26   93    85     a_GNB   a_SVC   a_LOG   a_RF
1000    0   100         34         98    55   34    33     a_LOG   a_RF   a_GNB   a_SVC
1000    0   100         35         47    45   45    46     a_LOG   a_SVC   a_RF   a_GNB

我可以這樣做，但似乎非常錯誤

UPDATE Targets
SET a_1st = CASE WHEN a_LOG >= a_RF and a_LOG >= a_GNB and a_LOG >= a_SVC THEN 'a_LOG'
WHEN a_RF >= a_LOG and a_RF >= a_GNB and a_RF >= a_SVC THEN 'a_RF'
WHEN a_GNB>= a_LOG and a_GNB >= a_RF and a_GNB >= a_SVC THEN 'a_GNB'
WHEN a_SVC>= a_LOG and a_SVC >= a_RF and a_SVC >= a_GNB THEN 'a_SVC' END

,a_2nd = I could not figure it out in an easy way !!
,a_3rd = I could not figure it out in an easy way !!
,a_4th = I could not figure it out in an easy way !!

我想知道是否有人有更好的方法來做到這一點

uj5u.com熱心網友回復：

您可以在 CROSS APPLY 中對資料進行 UNPIVOT 和 PIVOT

with cte as (
Select * 
 From  YourTable
 Cross Apply (
               Select d1 = max(case when RN=1 then item end)
                     ,d2 = max(case when RN=2 then item end)
                     ,d3 = max(case when RN=3 then item end)
                     ,d4 = max(case when RN=4 then item end)
                 From (
                       Select *
                             ,RN = row_number() over(order by value desc)
                        From ( values ('a_Log',a_Log)
                                     ,('a_RF' ,a_RF)
                                     ,('a_GNB',a_GNB)
                                     ,('a_Svc',a_Svc)
                             ) b(item,value) )B1
                      ) B2
)
Update cte
  set  a_1st = d1
      ,a_2nd = d2
      ,a_3rd = d3
      ,a_4th = d4

結果

uj5u.com熱心網友回復：

您可以使用Cross Apply with Values來取消透視列并使用行號應用排序，然后使用可更新的 CTE 來更新原始表：

with u as (
    select * 
    from targets
    cross apply (
        select 
            Max(case when rn=1 then c end) [1],
            max(case when rn=2 then c end) [2],
            max(case when rn=3 then c end) [3],
            max(case when rn=4 then c end) [4]
        from (
            select c, Row_Number() over(order by v desc) rn
            from (values(a_LOG, 'a_LOG'), (a_RF, 'a_RF'), (a_GNB, 'a_GNB'), (a_SVC, 'a_SVC') )v(v,c)
        )c
    )p
)
update u set
    u.a_1st=[1],
    u.a_2nd=[2],
    u.a_3rd=[3],
    u.a_4th=[4]

演示小提琴

標籤：sql sql-server 查询语句

上一篇：如何在T-SQL中轉義插入符號、波浪號和花括號？

下一篇：T-SQL查詢以獲得所需的輸出