SQL Server INNER JOIN查询问题：按商品颜色尺码获取对应库存

我正在学习SQL Server的INNER JOIN语法，需要查询每个商品按颜色和尺码区分的对应库存数量，但现有查询要么重复返回相同的库存值，要么使用GROUP BY分组后得到的求和结果均相同。

尝试的第一个查询（重复返回库存值） #

SELECT
    Product.Id,
    Product.Sku,
    Product.[Name] AS ProductName,
    a.[Name] AS Color,
    b.[Name] AS Size,
    ProductAttributeCombination.StockQuantity
FROM 
    Product
INNER JOIN 
    Product_ProductAttribute_Mapping m1 ON Product.Id = m1.ProductId
INNER JOIN 
    productAttributeValue a ON m1.Id = a.ProductAttributeMappingId
INNER JOIN 
    Product_ProductAttribute_Mapping m2 ON Product.Id = m2.ProductId
INNER JOIN 
    productAttributeValue b ON m2.Id = b.ProductAttributeMappingId
INNER JOIN 
    ProductAttributeCombination ON Product.Id = ProductAttributeCombination.ProductId 
WHERE 
    m1.ProductAttributeId = 1 
    AND m2.ProductAttributeId = 8 
    AND Product.Deleted = 0

查询结果： #

57  TEST_0005   Wrap Dress (Pack)   BLACK       S   236
57  TEST_0005   Wrap Dress (Pack)   BLACK       M   236
57  TEST_0005   Wrap Dress (Pack)   BLACK       L   236
57  TEST_0005   Wrap Dress (Pack)   RED         S   236
57  TEST_0005   Wrap Dress (Pack)   RED         M   236
57  TEST_0005   Wrap Dress (Pack)   RED         L   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE S   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE M   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE L   236
57  TEST_0005   Wrap Dress (Pack)   pre         S   236
57  TEST_0005   Wrap Dress (Pack)   pre         M   236
57  TEST_0005   Wrap Dress (Pack)   pre         L   236
57  TEST_0005   Wrap Dress (Pack)   BLACK       S   236
57  TEST_0005   Wrap Dress (Pack)   BLACK       M   236
57  TEST_0005   Wrap Dress (Pack)   BLACK       L   236
57  TEST_0005   Wrap Dress (Pack)   RED         S   236
57  TEST_0005   Wrap Dress (Pack)   RED         M   236
57  TEST_0005   Wrap Dress (Pack)   RED         L   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE S   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE M   236
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE L   236

尝试的第二个查询（GROUP BY后求和结果均相同） #

SELECT
    Product.Id,
    Product.Sku,
    Product.[Name] AS ProductName,
    a.[Name] AS Color,
    b.[Name] AS Size,
    SUM(ProductAttributeCombination.StockQuantity) AS TotalStockQuantity
FROM 
    Product
LEFT JOIN 
    Product_ProductAttribute_Mapping m1 ON Product.Id = m1.ProductId
LEFT JOIN 
    productAttributeValue a ON m1.Id = a.ProductAttributeMappingId
LEFT JOIN 
    Product_ProductAttribute_Mapping m2 ON Product.Id = m2.ProductId
LEFT JOIN 
    productAttributeValue b ON m2.Id = b.ProductAttributeMappingId
LEFT JOIN 
    ProductAttributeCombination ON Product.Id = ProductAttributeCombination.ProductId 
WHERE 
    m1.ProductAttributeId = 1 
    AND m2.ProductAttributeId = 8 
    AND Product.Deleted = 0
GROUP BY 
    Product.Id, Product.Sku, Product.[Name], a.[Name], b.[Name];

查询结果： #

57  TEST_0005   Wrap Dress (Pack)   BLACK           L   1800
57  TEST_0005   Wrap Dress (Pack)   BLACK       M   1800
57  TEST_0005   Wrap Dress (Pack)   BLACK       S   1800
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE L   1800
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE M   1800
57  TEST_0005   Wrap Dress (Pack)   BLACK/WHITE S   1800
57  TEST_0005   Wrap Dress (Pack)   pre     L   1800
57  TEST_0005   Wrap Dress (Pack)   pre     M   1800
57  TEST_0005   Wrap Dress (Pack)   pre     S   1800
57  TEST_0005   Wrap Dress (Pack)   RED     L   1800
57  TEST_0005   Wrap Dress (Pack)   RED     M   1800
57  TEST_0005   Wrap Dress (Pack)   RED     S   1800
58  TEST_0006   Romper (Broken)     BLACK       L   1295
58  TEST_0006   Romper (Broken)     BLACK       M   1295
58  TEST_0006   Romper (Broken)     BLACK       S   1295
58  TEST_0006   Romper (Broken)     LIGHT BLACK L   1295
58  TEST_0006   Romper (Broken)     LIGHT BLACK M   1295
58  TEST_0006   Romper (Broken)     LIGHT BLACK S   1295
58  TEST_0006   Romper (Broken)     RED     L   1295
58  TEST_0006   Romper (Broken)     RED     M   1295
58  TEST_0006   Romper (Broken)     RED     S   1295
63  NEW TEST    Maxi Dress (Pack)   BLACK       L   1752
63  NEW TEST    Maxi Dress (Pack)   BLACK       M   1752
63  NEW TEST    Maxi Dress (Pack)   BLACK       S   1752
63  NEW TEST    Maxi Dress (Pack)   BLACK/WHITE L   1752
63  NEW TEST    Maxi Dress (Pack)   BLACK/WHITE M   1752
63  NEW TEST    Maxi Dress (Pack)   BLACK/WHITE S   1752
63  NEW TEST    Maxi Dress (Pack)   RED     L   1752
63  NEW TEST    Maxi Dress (Pack)   RED     M   1752
63  NEW TEST    Maxi Dress (Pack)   RED     S   1752

问题核心 #

1. 笛卡尔积导致重复匹配：分别连接颜色、尺码的属性表，会让两种属性的所有组合与库存表全量关联，同一个库存记录被多次匹配，造成结果重复或求和时被多次累加。
2. 未关联属性组合与库存的对应关系：ProductAttributeCombination表存储的是具体属性组合（颜色+尺码）的库存，但你仅通过ProductId关联，没有把颜色、尺码的属性值和该表的组合绑定，导致所有属性组合都取到了商品的总库存。

解决方案 #

先获取商品的颜色+尺码属性组合，再关联对应库存：

SELECT
    p.Id,
    p.Sku,
    p.[Name] AS ProductName,
    colorVal.[Name] AS Color,
    sizeVal.[Name] AS Size,
    pac.StockQuantity
FROM 
    Product p
-- 关联颜色属性映射与属性值
INNER JOIN Product_ProductAttribute_Mapping colorMap 
    ON p.Id = colorMap.ProductId 
    AND colorMap.ProductAttributeId = 1
INNER JOIN productAttributeValue colorVal 
    ON colorMap.Id = colorVal.ProductAttributeMappingId
-- 关联尺码属性映射与属性值
INNER JOIN Product_ProductAttribute_Mapping sizeMap 
    ON p.Id = sizeMap.ProductId 
    AND sizeMap.ProductAttributeId = 8
INNER JOIN productAttributeValue sizeVal 
    ON sizeMap.Id = sizeVal.ProductAttributeMappingId
-- 关键：匹配属性组合与库存，通过XML提取属性值ID关联
INNER JOIN ProductAttributeCombination pac 
    ON p.Id = pac.ProductId
    AND pac.AttributeXML.value('(/Attributes/Attribute[@AttributeId="1"]/ValueId)[1]', 'INT') = colorVal.Id
    AND pac.AttributeXML.value('(/Attributes/Attribute[@AttributeId="8"]/ValueId)[1]', 'INT') = sizeVal.Id
WHERE 
    p.Deleted = 0

补充说明 #

如果ProductAttributeCombination表有直接存储颜色、尺码属性值ID的字段（比如ColorValueId、SizeValueId），可以替换XML解析部分，直接用字段等值关联，效率更高：

-- 替换库存关联部分
INNER JOIN ProductAttributeCombination pac 
    ON p.Id = pac.ProductId
    AND pac.ColorValueId = colorVal.Id
    AND pac.SizeValueId = sizeVal.Id

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