如何在SQL Server中按多组多对多变体筛选产品

我需要在SQL Server中按照一组多对多变体筛选产品表，单个变体的筛选很简单，但我不知道怎么实现多变体筛选。

表结构说明 #

PRODUCTS（产品表）

Variations（变体类型表）

VariationValues（变体值表）

ProductVariations（产品变体关联表）

单个变体筛选的可行代码 #

-- 筛选匹配指定风格的自行车（会找到Bike 1和Bike 4）
DECLARE @Style int = 1; -- MTB

SELECT p.Name, vv.ValueName
FROM Products p
INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
INNER JOIN VariationValues vv ON vv.Id = pv.VariationValueId
WHERE pv.VariationValueId = @Style
ORDER BY p.Name

多变体筛选的错误尝试 #

下面的查询无法得到结果，因为同一行的VariationValueId不可能同时等于多个不同的值：

DECLARE @Style int = 1; -- MTB
DECLARE @Colour int = 5; -- BLUE
DECLARE @WheelSize int =7; -- 26 inch

SELECT p.Name, vv.ValueName
FROM Products p
INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
INNER JOIN VariationValues vv ON vv.Id = pv.VariationValueId
WHERE pv.VariationValueId = @Style
AND pv.VariationValueId = @Colour
AND pv.VariationValueId = @WheelSize
ORDER BY p.Name

两种可行的多变体筛选方案 #

方案1：GROUP BY + HAVING（灵活适配任意数量的筛选条件）

将需要筛选的变体值存入临时表，通过分组统计匹配的变体数量来筛选产品：

DECLARE @FilterValues TABLE (ValueId INT);
INSERT INTO @FilterValues VALUES (1), (5), (7); -- 对应MTB、Blue、26 inch

SELECT p.Name
FROM Products p
INNER JOIN ProductVariations pv ON pv.ProductId = p.Id
WHERE pv.VariationValueId IN (SELECT ValueId FROM @FilterValues)
GROUP BY p.Id, p.Name
HAVING COUNT(DISTINCT pv.VariationValueId) = (SELECT COUNT(*) FROM @FilterValues)
ORDER BY p.Name;

这个方案的优势是不需要修改查询结构，只需调整@FilterValues中的值即可支持不同数量的筛选条件。

方案2：多次JOIN（筛选条件固定时更直观）

每个筛选的变体对应一次与ProductVariations表的JOIN，确保产品同时满足所有变体条件：

DECLARE @Style INT = 1; -- MTB
DECLARE @Colour INT = 5; -- Blue
DECLARE @WheelSize INT =7; -- 26 inch

SELECT DISTINCT p.Name
FROM Products p
INNER JOIN ProductVariations pv1 ON pv1.ProductId = p.Id AND pv1.VariationValueId = @Style
INNER JOIN ProductVariations pv2 ON pv2.ProductId = p.Id AND pv2.VariationValueId = @Colour
INNER JOIN ProductVariations pv3 ON pv3.ProductId = p.Id AND pv3.VariationValueId = @WheelSize
ORDER BY p.Name;

使用DISTINCT避免重复返回同一产品，适合筛选条件数量固定的场景，逻辑清晰易懂。

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