这是达到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 = true
Item 1 SubItem 1-1 = true, SubItem 1-2 = true, SubItem 1-3 = false
Item 1 SubItem 1-1 = true, SubItem 1-2 = false, SubItem 1-3 = true
Item 1 SubItem 1-1 = true, SubItem 1-2 = false, SubItem 1-3 = false
Item 1 SubItem 1-1 = false, SubItem 1-2 = true, SubItem 1-3 = true
Item 1 SubItem 1-1 = false, SubItem 1-2 = true, SubItem 1-3 = false
Item 1 SubItem 1-1 = false, SubItem 1-2 = false, SubItem 1-3 = true
Item 1 SubItem 1-1 = false, SubItem 1-2 = false, SubItem 1-3 = false
Item 2 SubItem 2-1 = true
Item 2 SubItem 2-1 = false
Item 3 SubItem 3-1 = true, SubItem 3-2 = true
Item 3 SubItem 3-1 = true, SubItem 3-2 = false
Item 3 SubItem 3-1 = false, SubItem 3-2 = true
Item 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 #x
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 c
CROSS 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.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);
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));
您可能应该根据实际数据/架构测试这两种变体。
602392714
清零编程群