使用SQL Server创建所有可能的组合

使用SQL Server创建所有可能的组合

这是达到4级的方法（您的样本数据仅需要3级，但我想确保它能超出此范围）。您应该能够遵循模式上升到7、10，这是您的一切。哦，别指望这会很快。

;WITH z AS (  SELECt i,inm,si,snm,truth,c FROM   (    SELECt i = i.ItemId, inm = i.Name, si = isi.SubItemId, snm = s.Name,      c = COUNT(isi.SubItemId) OVER (PARTITION BY i.ItemId)    FROM @Item_SubItem AS isi    INNER JOIN @Item AS i    ON isi.ItemId    = i.ItemId    INNER JOIN @SubItem AS s ON isi.SubItemId = s.SubItemId  ) AS y  CROSS JOIN (VALUES('true'),('false')) AS t(truth))SELECt Item = z1.inm,   SubItems = COALESCE(       z1.snm + ' = ' + z1.truth,'')+ COALESCE(', ' + z2.snm + ' = ' + z2.truth,'')+ COALESCE(', ' + z3.snm + ' = ' + z3.truth,'')+ COALESCE(', ' + z4.snm + ' = ' + z4.truth,'')FROM z AS z1  LEFT OUTER JOIN z AS z2     ON z1.i = z2.i AND z1.si < z2.si  LEFT OUTER JOIN z AS z3     ON z2.i = z3.i AND z2.si < z3.si   LEFT OUTER JOIN z AS z4     ON z3.i = z4.i AND z3.si < z4.si   WHERe (z1.c = 1)     OR (z1.c = 2 AND z2.i IS NOT NULL)    OR (z1.c = 3 AND z3.i IS NOT NULL)    OR (z1.c = 4 AND z4.i IS NOT NULL);

结果给出了样本数据：

Item        SubItems------      ---------------------------------------------------------------Item 1      SubItem 1-1 = true, SubItem 1-2 = true, SubItem 1-3 = trueItem 1      SubItem 1-1 = true, SubItem 1-2 = true, SubItem 1-3 = falseItem 1      SubItem 1-1 = true, SubItem 1-2 = false, SubItem 1-3 = trueItem 1      SubItem 1-1 = true, SubItem 1-2 = false, SubItem 1-3 = falseItem 1      SubItem 1-1 = false, SubItem 1-2 = true, SubItem 1-3 = trueItem 1      SubItem 1-1 = false, SubItem 1-2 = true, SubItem 1-3 = falseItem 1      SubItem 1-1 = false, SubItem 1-2 = false, SubItem 1-3 = trueItem 1      SubItem 1-1 = false, SubItem 1-2 = false, SubItem 1-3 = falseItem 2      SubItem 2-1 = trueItem 2      SubItem 2-1 = falseItem 3      SubItem 3-1 = true, SubItem 3-2 = trueItem 3      SubItem 3-1 = true, SubItem 3-2 = falseItem 3      SubItem 3-1 = false, SubItem 3-2 = trueItem 3      SubItem 3-1 = false, SubItem 3-2 = false

*经过一番思考后进行 *编辑
，与先将一堆信息转储到#temp表中相比，我进行了测试，尽管顺序不同（似乎仍然按顺序排列，

ItemId

但错误值排序较高），这似乎可以更好地进行优化：

SELECt c.i, c.inm, c.si, c.snm, c.c, t.truth INTO #xFROM (  SELECt      i = i.ItemId, inm = i.Name, si = isi.SubItemId, snm = s.Name,     c = COUNT(isi.SubItemId) OVER (PARTITION BY i.ItemId)  FROM @Item_SubItem AS isi  INNER JOIN @Item AS i    ON isi.ItemId    = i.ItemId  INNER JOIN @SubItem AS s ON isi.SubItemId = s.SubItemId) AS cCROSS JOIN (VALUES('true'),('false')) AS t(truth);CREATE UNIQUE CLUSTERED INDEX x ON #x(i,si,truth);SELECt   Item = z1.inm,   SubItems = COALESCE(       z1.snm + ' = ' + z1.truth,'')+ COALESCE(', ' + z2.snm + ' = ' + z2.truth,'')+ COALESCE(', ' + z3.snm + ' = ' + z3.truth,'')+ COALESCE(', ' + z4.snm + ' = ' + z4.truth,'')FROM #x AS z1  LEFT OUTER JOIN #x AS z2 ON z1.i = z2.i AND z1.si < z2.si  LEFT OUTER JOIN #x AS z3 ON z2.i = z3.i AND z2.si < z3.si  LEFT OUTER JOIN #x AS z4 ON z3.i = z4.i AND z3.si < z4.siWHERe (z1.c = 1)   OR (z1.c = 2 AND z2.i IS NOT NULL)  OR (z1.c = 3 AND z3.i IS NOT NULL)  OR (z1.c = 4 AND z4.i IS NOT NULL);DROP TABLE #x;

如果基础表已建立索引，则原始版本将更为有利，例如

DECLARE @Item TABLE (ItemId int PRIMARY KEY, Name varchar(50));DECLARE @Item_SubItem TABLE (ItemId int, SubitemId int,   PRIMARY KEY (ItemId,SubItemId));DECLARE @SubItem TABLE (SubitemId int PRIMARY KEY, Name varchar(50));

您可能应该根据实际数据/架构测试这两种变体。