sql-server - SQL Server 中 CASE 的替代方案

Question

我有以下两个表:

 CREATE TABLE [dbo].[TAB1](
     [COIL_ID] [varchar](25) NOT NULL,
     [COIL_DATE] [datetime] NULL,
     [GRADE] [float] NULL,
     [QUALITY] [float] NULL,
     [FURNACE_NUM] [float] NULL,
     [FM_GAUGE] [float] NULL,
     [FM_WIDTH] [float] NULL,
     [FINTEMP_AIM] [float] NULL,
     [SLAB_GAUGE] [float] NULL,
     [SLAB_WIDTH] [float] NULL,
     [SLAB_LENGTH] [float] NULL,
     [DO_TEMP] [float] NULL,
     [GAP_REFERENCE_PR1_1] [float] NULL,
     [GAP_REFERENCE_PR1_2] [float] NULL,
     [GAP_REFERENCE_PR1_3] [float] NULL,
     [GAP_REFERENCE_PR1_4] [float] NULL,
     [GAP_REFERENCE_PR1_5] [float] NULL,
     [GAP_REFERENCE_PR1_6] [float] NULL,
     [GAP_REFERENCE_PR1_7] [float] NULL,
     [GAP_REFERENCE_PR1_8] [float] NULL,
     [GAP_REFERENCE_PR1_9] [float] NULL,
     [GAP_REFERENCE_PR1_10] [float] NULL,
     [GAP_REFERENCE_PR1_11] [float] NULL,
     [GAP_REFERENCE_PR1_12] [float] NULL,
     [GAP_REFERENCE_PR1_13] [float] NULL,
     [GAP_REFERENCE_PR1_14] [float] NULL,
     [GAP_REFERENCE_PR1_15] [float] NULL,
     [GAP_REFERENCE_PR1_16] [float] NULL,
     [GAP_REFERENCE_PR1_17] [float] NULL,
     [GAP_REFERENCE_PR1_18] [float] NULL,
     [GAP_REFERENCE_PR1_19] [float] NULL,
     [GAP_REFERENCE_PR1_20] [float] NULL,
     [GAP_REFERENCE_PR1_21] [float] NULL,
     [GAP_REFERENCE_PR1_22] [float] NULL,
     [GAP_REFERENCE_PR1_23] [float] NULL,
     [GAP_REFERENCE_PR1_24] [float] NULL,
     [GAP_REFERENCE_PR1_25] [float] NULL
 ) 


 CREATE TABLE [dbo].[TAB2](
     [COIL_ID] [varchar](25) NOT NULL,
     [_SegmentNr] [int] NOT NULL,
     [HRM Metal In Mill] [real] NULL,
     [HRM Screws Homed] [real] NULL,
     [HRM Tracking Current Pass Number] [real] NULL,
     [Topmotor_ArmCur] [real] NULL,
     [Max_Topmtr_Armcur_pass] [real] NULL,
     [Botmotor_ArmCur] [real] NULL,
     [Max_Botmtr_Armcur_pass] [real] NULL,
     [SPEED] [real] NULL,
     [POWER] [real] NULL,
     [TEMP] [real] NULL,
     [T_Load] [real] NULL,
     [D_Load] [real] NULL,
     [PosFb] [real] NULL,
     [Dif_Pos] [real] NULL,
     [Guide_Entry] [real] NULL,
     [Guide_Exit] [real] NULL,
     [Thread_Achi] [real] NULL
 ) 

在 [dbo].[TAB1] 中会有一行表示 coild-id，在 [dbo].[TAB2] 中会有多行。

我需要的输出如下:

COIL-ID  HRM-Tracking-Current-Pass-Number      GAP
 1             1                              value of GAP_REFERENCE_PR1_1                     
 1             2                              value of GAP_REFERENCE_PR1_2                         
 1             3                              value of GAP_REFERENCE_PR1_3

我目前正在使用以下代码:

select a.coil_id Coil_ID, b.[HRM Tracking Current Pass Number] ,
max(case b.[HRM Tracking Current Pass Number]
when 1   THEN a.EXIT_GAUGE_PR1_1
when 2   THEN a.EXIT_GAUGE_PR1_2
when 3   THEN a.EXIT_GAUGE_PR1_3
when 4   THEN a.EXIT_GAUGE_PR1_4
when 5   THEN a.EXIT_GAUGE_PR1_5
when 6   THEN a.EXIT_GAUGE_PR1_6
when 7   THEN a.EXIT_GAUGE_PR1_7
when 8   THEN a.EXIT_GAUGE_PR1_8
when 9   THEN a.EXIT_GAUGE_PR1_9
when 10  THEN a.EXIT_GAUGE_PR1_10
when 11  THEN a.EXIT_GAUGE_PR1_11
when 12  THEN a.EXIT_GAUGE_PR1_12
when 13  THEN a.EXIT_GAUGE_PR1_13
when 14  THEN a.EXIT_GAUGE_PR1_14
when 15  THEN a.EXIT_GAUGE_PR1_15
when 16  THEN a.EXIT_GAUGE_PR1_16
when 17  THEN a.EXIT_GAUGE_PR1_17
when 18  THEN a.EXIT_GAUGE_PR1_18
when 19  THEN a.EXIT_GAUGE_PR1_19
when 20  THEN a.EXIT_GAUGE_PR1_20
when 21  THEN a.EXIT_GAUGE_PR1_21
when 22  THEN a.EXIT_GAUGE_PR1_22
when 23  THEN a.EXIT_GAUGE_PR1_23
when 24  THEN a.EXIT_GAUGE_PR1_24
when 25  THEN a.EXIT_GAUGE_PR1_25
END)  as EXIT_GAUGE,

我想知道是否有更有效的方式来编写查询。

在此先感谢您的帮助。

Answer 1

从 SQL Server 2012 开始，有一个 CHOOSE方法:

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;  

Result  
-------------  
Developer  

所以在你的情况下，你可能会像这样使用它:

SELECT MAX(
    CHOOSE(
        b.[HRM Tracking Current Pass Number],
        a.EXIT_GAUGE_PR1_1,
        a.EXIT_GAUGE_PR1_2,
        a.EXIT_GAUGE_PR1_3,
        ...
    )
)