SQL Server 2017：匹配无序含重复维度列并更新dimension_id

我拥有purchases和dimensions两张表，二者均包含height、length、width三列。dimensions表中有约120种不考虑顺序的唯一维度组合（如存在10,12,10则不会有10,10,12）。
purchases表中的维度存储顺序可能不同（例如dimensions中为10,10,12，purchases中可能是10,12,10或12,10,10）。
现有方法均忽略重复值，但10,10,12与10,12,12应视为不同组合，无法适配这些方案。

需求：通过SQL更新purchases表中每条记录的dimension_id，无对应维度组合时保持为NULL。

表结构示例：

-- dimensions表
id    height    width    length
1     10        10       12
2     10        12       12

-- purchases表
order_number    height    width    length    dimension_id
1               10        12       10        NULL  <- 更新为1
2               10        12       12        NULL  <- 更新为2
3               10        12       15        NULL  <- 保持NULL

数据库版本：Microsoft SQL Server 2017

针对SQL Server 2017，可以通过标准化维度值的排序顺序实现精准匹配——将每个记录的三个维度值按从小到大排序，提取最小值、中间值、最大值，再通过这三个标准化后的值关联两张表，既忽略原始存储顺序，又能保留重复值的差异（比如10,10,12和10,12,12会被识别为不同组合）。

方案一：CTE+UNPIVOT标准化维度 #

使用公共表表达式统一处理两张表的维度排序，再关联更新：

WITH StandardizedDimensions AS (
    SELECT 
        id,
        MIN(val) OVER (PARTITION BY id) AS min_dim,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY id) AS mid_dim,
        MAX(val) OVER (PARTITION BY id) AS max_dim
    FROM dimensions
    UNPIVOT (
        val FOR dim IN (height, width, length)
    ) AS unpvt
),
StandardizedPurchases AS (
    SELECT 
        order_number,
        MIN(val) OVER (PARTITION BY order_number) AS min_dim,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY order_number) AS mid_dim,
        MAX(val) OVER (PARTITION BY order_number) AS max_dim
    FROM purchases
    UNPIVOT (
        val FOR dim IN (height, width, length)
    ) AS unpvt
)
UPDATE p
SET p.dimension_id = sd.id
FROM purchases p
JOIN StandardizedPurchases sp ON p.order_number = sp.order_number
LEFT JOIN StandardizedDimensions sd 
    ON sp.min_dim = sd.min_dim
    AND sp.mid_dim = sd.mid_dim
    AND sp.max_dim = sd.max_dim
WHERE p.dimension_id IS NULL;

思路说明

1. UNPIVOT转换：将单条记录的三个维度列转为多行，方便统一计算排序后的值；
2. 标准化维度：用MIN()/MAX()提取极值，用PERCENTILE_CONT(0.5)计算中间值（三个数排序后的第二个值），完整保留维度组合的特征；
3. 关联更新：通过标准化后的三个值匹配两张表，仅更新未匹配的dimension_id。

方案二：枚举排列组合（简洁直观） #

由于只有三个维度，直接枚举所有6种排列组合进行匹配，代码更直观：

UPDATE p
SET p.dimension_id = d.id
FROM purchases p
LEFT JOIN dimensions d 
    ON (
        (p.height = d.height AND p.width = d.width AND p.length = d.length)
        OR (p.height = d.height AND p.width = d.length AND p.length = d.width)
        OR (p.height = d.width AND p.width = d.height AND p.length = d.length)
        OR (p.height = d.width AND p.width = d.length AND p.length = d.height)
        OR (p.height = d.length AND p.width = d.height AND p.length = d.width)
        OR (p.height = d.length AND p.width = d.width AND p.length = d.height)
    )
WHERE p.dimension_id IS NULL;

该方案适合维度数量少的场景，结合dimensions表仅120条数据的规模，性能不会有明显问题。

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