如何编写SQL查询实现字母与对应因子的关联？求最优方案及示例

首先，先明确你的数据结构和需求：你需要将同一关联分组内的字母行与对应的因子值行按顺序一一关联，确保每个字母匹配到正确的因子。

整理后的原始数据表格 #

从数据来看，AD*A*1是已完成关联的示例，AD*A*2的Data值对应AD*A*1的NEW_ADD列，AD*A*3的Data值对应AD*A*1的Col1列。我们需要将这些因子值按顺序匹配到对应的字母行上。

为什么选ROW_NUMBER()而非DENSE_RANK() #

• ROW_NUMBER()：会在每个分组内为每行生成唯一的连续序号，哪怕行内值重复，序号也不会重复。这完全契合我们按顺序精准匹配的需求，能保证每个字母和对应的因子行一一对应。
• DENSE_RANK()：如果分组内有重复值，会生成相同的排名，这会导致匹配时出现一对多的错误，无法保证顺序的准确性。

查询示例 #

假设你的表名为your_table，我们可以通过以下步骤实现关联：

1. 为每个ADD_Col分组内的行按OrderId降序分配行号（OrderId递减的顺序正好对应字母和因子的匹配顺序）。
2. 拆分出字母行、NEW_ADD因子行、Col1因子行三类数据。
3. 通过共同的分组前缀和行号完成关联。

WITH numbered_rows AS (
    SELECT 
        ADD_Col,
        Data,
        OrderId,
        -- 提取ADD_Col的前缀（比如AD*A*），用于关联同一组的字母和因子行
        LEFT(ADD_Col, CHARINDEX('*', ADD_Col, CHARINDEX('*', ADD_Col)+1)) AS group_prefix,
        -- 提取ADD_Col的类型标识（1=字母行，2=NEW_ADD因子，3=Col1因子）
        RIGHT(ADD_Col, 1) AS row_type,
        -- 每个ADD_Col分组内按OrderId降序生成行号
        ROW_NUMBER() OVER (PARTITION BY ADD_Col ORDER BY OrderId DESC) AS rn
    FROM your_table
),
letter_rows AS (
    SELECT group_prefix, rn, Data AS letter
    FROM numbered_rows
    WHERE row_type = '1'
),
new_add_rows AS (
    SELECT group_prefix, rn, Data AS new_add_value
    FROM numbered_rows
    WHERE row_type = '2'
),
col1_rows AS (
    SELECT group_prefix, rn, Data AS col1_value
    FROM numbered_rows
    WHERE row_type = '3'
)
SELECT 
    lr.group_prefix + '1' AS ADD_Col,
    lr.letter AS Data,
    -- 关联原表的OrderId
    (SELECT OrderId FROM your_table WHERE ADD_Col = lr.group_prefix + '1' AND ROW_NUMBER() OVER (ORDER BY OrderId DESC) = lr.rn) AS OrderId,
    lr.letter AS Output,
    nar.new_add_value AS NEW_ADD,
    cr.col1_value AS Col1
FROM letter_rows lr
JOIN new_add_rows nar ON lr.group_prefix = nar.group_prefix AND lr.rn = nar.rn
JOIN col1_rows cr ON lr.group_prefix = cr.group_prefix AND lr.rn = cr.rn
ORDER BY lr.group_prefix, lr.rn DESC;

简化版（同一ADD_Col下包含字母和因子行） #

如果你的数据是每个ADD_Col下同时包含4行字母和4行因子，可以用更简洁的写法：

WITH numbered_rows AS (
    SELECT 
        ADD_Col,
        Data,
        OrderId,
        Output,
        -- 每个ADD_Col内按OrderId降序生成行号
        ROW_NUMBER() OVER (PARTITION BY ADD_Col ORDER BY OrderId DESC) AS rn
    FROM your_table
),
letter_part AS (
    SELECT ADD_Col, rn, Data AS letter, Output
    FROM numbered_rows
    WHERE rn <= 4 -- 前4行是字母行
),
factor_part AS (
    SELECT ADD_Col, rn - 4 AS factor_rn, Data AS factor_value
    FROM numbered_rows
    WHERE rn > 4 -- 后4行是因子行
)
SELECT 
    lp.ADD_Col,
    lp.letter AS Data,
    (SELECT OrderId FROM numbered_rows WHERE ADD_Col = lp.ADD_Col AND rn = lp.rn) AS OrderId,
    lp.Output,
    -- 按因子行顺序匹配对应列
    CASE WHEN fp.factor_rn = 1 THEN fp.factor_value END AS NEW_ADD,
    CASE WHEN fp.factor_rn = 2 THEN fp.factor_value END AS Col1
FROM letter_part lp
JOIN factor_part fp ON lp.ADD_Col = fp.ADD_Col AND lp.rn = fp.factor_rn
ORDER BY lp.ADD_Col, lp.rn DESC;

关键说明 #

核心逻辑是利用ROW_NUMBER()生成的唯一行号，确保字母和因子行的顺序完全匹配。如果需要匹配更多因子列，只需要按行号关联对应的因子行即可。

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