如何在Toad Point SQL中为银行贷款数据生成类型映射列

方法一：直接使用CASE WHEN语句 #

这是最直观的方式，把类型映射逻辑直接整合到现有查询中，适合映射规则较少的场景：

SELECT 
    Loan,
    REPLACE(REPLACE(REPLACE(Loan, '3',''), '2',''),'1','') AS [Description],
    CASE 
        WHEN REPLACE(REPLACE(REPLACE(Loan, '3',''), '2',''),'1','') = 'BL' THEN 'Business'
        WHEN REPLACE(REPLACE(REPLACE(Loan, '3',''), '2',''),'1','') = 'CC' THEN 'Credit Card'
        -- 后续新增映射规则，直接添加WHEN条件即可
        ELSE 'Other' -- 处理未匹配到的默认情况
    END AS [Type]
FROM Banking_Data

方法二：用子查询/CROSS APPLY简化冗余代码 #

如果觉得重复写三次REPLACE太繁琐，可以先通过子查询算出Description，再基于它判断类型，让代码更简洁：

SELECT 
    b.Loan,
    d.[Description],
    CASE 
        WHEN d.[Description] = 'BL' THEN 'Business'
        WHEN d.[Description] = 'CC' THEN 'Credit Card'
        ELSE 'Other'
    END AS [Type]
FROM Banking_Data b
CROSS APPLY (
    SELECT REPLACE(REPLACE(REPLACE(b.Loan, '3',''), '2',''),'1','') AS [Description]
) d

方法三：创建映射表维护规则（适合大量映射场景） #

如果后续需要维护大量类型映射，建议创建专门的映射表，避免频繁修改主查询：

1. 先创建并初始化映射表：

CREATE TABLE LoanTypeMapping (
    Description VARCHAR(10) PRIMARY KEY,
    [Type] VARCHAR(50) NOT NULL
)

INSERT INTO LoanTypeMapping VALUES ('BL', 'Business'), ('CC', 'Credit Card')

2. 通过LEFT JOIN关联查询获取类型：

SELECT 
    b.Loan,
    d.[Description],
    ISNULL(m.[Type], 'Other') AS [Type]
FROM Banking_Data b
CROSS APPLY (
    SELECT REPLACE(REPLACE(REPLACE(b.Loan, '3',''), '2',''),'1','') AS [Description]
) d
LEFT JOIN LoanTypeMapping m ON d.[Description] = m.Description

Toad Point支持标准SQL语法，以上三种方法均可直接使用。

内容的提问来源于stack exchange，提问作者Coding_Nubie