使用 "REPLACE" 匹配列名，然后联接表 - SSMS

Question

我有下面的表-称之为Table1:

SIMULATION  PRODUCT               PERIOD    OUTPUT_OL_EstRes
0           USSIC_Aviation        2012Q3    25787698
0           Avemco_Aviation       2012Q3    13374366
0           HC_Houston_Aviation   2012Q3    41626488
0           Other_Aviation        2012Q3    700829
0           Disability            2012Q3    8566175

我正试图加入下表——称之为Table2:

RptLOB                RptLOBPrintOrder  RptSegt
USSIC Aviation        1                 US Property & Casualty
Avemco Aviation       2                 US Property & Casualty
HC Houston Aviation   3                 US Property & Casualty
Other Aviation        4                 US Property & Casualty
Disability            5                 US Property & Casualty

使用以下代码：

select a.SIMULATION, REPLACE(a.PRODUCT,'_',' ') as PRODUCT, a.PERIOD, a.OUTPUT_OL_EstRes , b.RptLOBPrintOrder, b.RptSegt
from Table1 as a
left join Table2 as b
on a.PRODUCT=b.RptLOB

并且我在RptLABPrintOrder和RptSgt列上得到NULL值（除了值Disability，因为我不必去掉下划线）。我确保去掉了"_"，所以我不太确定为什么会发生这种情况。输出表如下：

SIMULATION  PRODUCT                 PERIOD  OUTPUT_OL_EstRes    RptLOBPrintOrder    RptSegt
0           USSIC Aviation          2012Q3  25787698            NULL                NULL
0           Avemco Aviation         2012Q3  13374366            NULL                NULL
0           HC Houston Aviation     2012Q3  41626488            NULL                NULL
0           Other Aviation          2012Q3  700829              NULL                NULL
0           Disability              2012Q3  8566175             5                   US Property & Casualty

任何帮助都将不胜感激！有更好的方法吗？

Answer 1

查询：

SQLFIDDLEXample

SELECT a.SIMULATION,
       REPLACE(a.PRODUCT,'_',' ') AS PRODUCT,
       a.PERIOD,
       a.OUTPUT_OL_EstRes ,
       b.RptLOBPrintOrder,
       b.RptSegt
FROM Table1 AS a
LEFT JOIN Table2 AS b ON REPLACE(a.PRODUCT,'_',' ')=b.RptLOB

结果：

| SIMULATION |             PRODUCT | PERIOD | OUTPUT_OL_ESTRES | RPTLOBPRINTORDER |                RPTSEGT |
------------------------------------------------------------------------------------------------------------
|          0 |      USSIC Aviation | 2012Q3 |         25787698 |                1 | US Property & Casualty |
|          0 |     Avemco Aviation | 2012Q3 |         13374366 |                2 | US Property & Casualty |
|          0 | HC Houston Aviation | 2012Q3 |         41626488 |                3 | US Property & Casualty |
|          0 |      Other Aviation | 2012Q3 |           700829 |                4 | US Property & Casualty |
|          0 |          Disability | 2012Q3 |          8566175 |                5 | US Property & Casualty |