sql - 从sql server 2008中的多个值中选择值

Question

DECLARE @ProductFeature TABLE (ProductID int, FeatureID int)

INSERT INTO @ProductFeature
  SELECT 1,100
    UNION ALL
  SELECT 1,101
    UNION ALL
  SELECT 1,102
    UNION ALL
  SELECT 2,103
    UNION ALL
  SELECT 2,104
    UNION ALL
  SELECT 3,100
    UNION ALL
  SELECT 3,101
    UNION ALL
  SELECT 3,102
    UNION ALL
  SELECT 4,102
    UNION ALL
  SELECT 4,101
    UNION ALL
  SELECT 5,110
    UNION ALL
  SELECT 5,100
    UNION ALL
  SELECT 5,101

我的要求是如果我通过 ProductID = 1 , 那么我必须选择 Product具有类似于 ProductID = 1 的功能.

自 ProductID = 1有 3 个特征(100,101,102)，只有 ProductID = 3它具有相同的计数和功能，具有 ProductID = 1

预期结果

ProductID   FeatureID
3              100
3              101
3              102

Answer 1

带有 EXCEPT 的选项操作

DECLARE @ProductID int = 1

SELECT ProductID, FeatureID
FROM ProductFeature p1
WHERE p1.ProductID != @ProductID AND 
      NOT EXISTS (
                  SELECT p2.FeatureID         
                  FROM ProductFeature p2
                  WHERE p2.ProductID = @ProductID                                                
                  EXCEPT
                  SELECT p3.FeatureID
                  FROM ProductFeature p3
                  WHERE p3.ProductID = p1.ProductID               
                  )