关联ProductAttributeCombination结果重复，如何匹配AttributeXml获取库存？

需要查询获取以下字段：

p.Id, 
p.Sku, 
p.[Name], 
v1.[Name] AS Color, 
v2.[Name] AS Size, 
c1.StockQuantity

当前使用的查询语句（未关联库存表时正常，关联后出现重复）：

SELECT 
p.Id, 
p.Sku, 
p.[Name], 
v1.[Name] AS Color, 
v2.[Name] AS Size
-- , c1.StockQuantity
FROM Product p
INNER JOIN Product_ProductAttribute_Mapping m1 ON m1.ProductId = p.Id 
INNER JOIN Product_ProductAttribute_Mapping m2 ON m2.ProductId = p.Id 
INNER JOIN ProductAttribute a1 ON a1.Id = m1.ProductAttributeId AND a1.[Name] = 'Color'
INNER JOIN ProductAttribute a2 ON a2.Id = m2.ProductAttributeId AND a2.[Name] = 'Size'
INNER JOIN ProductAttributeValue v1 ON v1.ProductAttributeMappingId = m1.Id
INNER JOIN ProductAttributeValue v2 ON v2.ProductAttributeMappingId = m2.Id
--  INNER JOIN ProductAttributeCombination c1 ON c1.ProductId = p.Id 
WHERE p.Deleted = '0' 

重复原因 #

仅通过ProductId关联ProductAttributeCombination会产生笛卡尔积：每个产品的属性组合（库存记录）会和所有颜色、尺寸的组合进行匹配，导致同一条颜色尺寸记录对应多条库存记录，出现重复。

解决方案 #

需要解析ProductAttributeCombination.AttributeXml中的颜色和尺寸属性值ID，与ProductAttributeValue的ID进行精准匹配，确保每个颜色尺寸组合只对应正确的库存记录。

以SQL Server为例，使用XML函数提取AttributeXml中的属性值，修改后的完整查询语句：

SELECT 
    p.Id, 
    p.Sku, 
    p.[Name], 
    v1.[Name] AS Color, 
    v2.[Name] AS Size, 
    c1.StockQuantity
FROM Product p
INNER JOIN Product_ProductAttribute_Mapping m1 ON m1.ProductId = p.Id 
INNER JOIN ProductAttribute a1 ON a1.Id = m1.ProductAttributeId AND a1.[Name] = 'Color'
INNER JOIN ProductAttributeValue v1 ON v1.ProductAttributeMappingId = m1.Id
INNER JOIN Product_ProductAttribute_Mapping m2 ON m2.ProductId = p.Id 
INNER JOIN ProductAttribute a2 ON a2.Id = m2.ProductAttributeId AND a2.[Name] = 'Size'
INNER JOIN ProductAttributeValue v2 ON v2.ProductAttributeMappingId = m2.Id
INNER JOIN ProductAttributeCombination c1 
    ON c1.ProductId = p.Id 
    -- 匹配颜色属性值ID
    AND c1.AttributesXml.value('(/Attributes/ProductAttribute[@ID=sql:column("a1.Id")]/ProductAttributeValue/Value)[1]', 'INT') = v1.Id
    -- 匹配尺寸属性值ID
    AND c1.AttributesXml.value('(/Attributes/ProductAttribute[@ID=sql:column("a2.Id")]/ProductAttributeValue/Value)[1]', 'INT') = v2.Id
WHERE p.Deleted = '0' 

关键点说明 #

1. 使用sql:column("a1.Id")动态引用颜色属性的ID，避免硬编码，适配不同产品的属性ID差异
2. 通过XML路径/Attributes/ProductAttribute[@ID=...]/ProductAttributeValue/Value精准提取对应属性的Value值（即属性值ID）
3. 关联时同时匹配ProductId、颜色属性值ID、尺寸属性值ID，确保每个颜色尺寸组合只关联对应的库存记录，消除重复

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