如何按客户返回Table A最低分2个合格产品列名（关联Table B资格）

核心思路 #

先将Table A的宽表结构（产品为列）转换为窄表结构（产品为行），统一处理每个产品的得分，再关联Table B过滤客户有资格的产品，最后用窗口函数按客户分组，筛选得分最低的前2个产品。这种方法比堆砌CASE语句更简洁易维护，性能也更优。

具体实现（以SQL为例） #

假设Table A结构为：CUST_NO, PROD_A_SCORE, PROD_B_SCORE, PROD_C_SCORE（含客户ID及各产品得分）；Table B结构为：InvolvedPartyId_Numeric_ID, PROD_A_ELIGIBLE, PROD_B_ELIGIBLE, PROD_C_ELIGIBLE（含客户ID及各产品资格标记，1表示有资格）。

1. 转置Table A为窄表 #

用UNION ALL把每个产品列转为独立行，保留客户ID与对应得分：

SELECT 
    CUST_NO,
    'PROD_A' AS PRODUCT_NAME,
    PROD_A_SCORE AS SCORE
FROM TableA
UNION ALL
SELECT 
    CUST_NO,
    'PROD_B' AS PRODUCT_NAME,
    PROD_B_SCORE AS SCORE
FROM TableA
UNION ALL
SELECT 
    CUST_NO,
    'PROD_C' AS PRODUCT_NAME,
    PROD_C_SCORE AS SCORE
FROM TableA
-- 有更多产品时，继续添加UNION ALL分支即可

2. 关联Table B筛选合格产品 #

将转置后的表与Table B通过客户ID关联，过滤出客户有资格的产品：

WITH transposed_a AS (
    -- 插入上面的转置SQL
    SELECT 
        CUST_NO,
        'PROD_A' AS PRODUCT_NAME,
        PROD_A_SCORE AS SCORE
    FROM TableA
    UNION ALL
    SELECT 
        CUST_NO,
        'PROD_B' AS PRODUCT_NAME,
        PROD_B_SCORE AS SCORE
    FROM TableA
    UNION ALL
    SELECT 
        CUST_NO,
        'PROD_C' AS PRODUCT_NAME,
        PROD_C_SCORE AS SCORE
    FROM TableA
),
eligible_products AS (
    SELECT 
        t.CUST_NO,
        t.PRODUCT_NAME,
        t.SCORE
    FROM transposed_a t
    JOIN TableB b ON t.CUST_NO = b.InvolvedPartyId_Numeric_ID
    WHERE 
        CASE t.PRODUCT_NAME
            WHEN 'PROD_A' THEN b.PROD_A_ELIGIBLE
            WHEN 'PROD_B' THEN b.PROD_B_ELIGIBLE
            WHEN 'PROD_C' THEN b.PROD_C_ELIGIBLE
            -- 新增产品时，补充对应资格字段的匹配逻辑
        END = 1
)

3. 筛选每个客户得分最低的前2个产品 #

用窗口函数给每个客户的产品按得分排序，取排名前2的记录：

SELECT 
    CUST_NO,
    PRODUCT_NAME,
    SCORE
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY CUST_NO ORDER BY SCORE ASC) AS score_rank
    FROM eligible_products
) ranked
WHERE score_rank <= 2

额外优化点 #

• 若存在多个产品得分并列最低的情况，想保留所有并列项，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。
• 部分数据库（如SQL Server、Oracle）支持UNPIVOT语法，可替代UNION ALL实现更简洁的宽表转置：

  SELECT CUST_NO, PRODUCT_NAME, SCORE
  FROM TableA
  UNPIVOT (
      SCORE FOR PRODUCT_NAME IN (PROD_A_SCORE, PROD_B_SCORE, PROD_C_SCORE)
  ) AS unpivoted
  

  注意：使用UNPIVOT要求所有得分列的数据类型一致。

内容的提问来源于stack exchange，提问作者J O'Donnell