我在这里看到过类似的问题，但是要么我没有得到答案，要么他们没有适用……这就是我所需要的，并认为这真的很简单：我有一组项目，每个项目都有一个子项目集。每个项目的子项目数会发生变化。例如：
Item 1 SubItem 1-1 SubItem 1-2 SubItem 1-3 Item 2 SubItem 2-1 Item 3 SubItem 3-1 SubItem 3-2
对于一个非常特定的用途，我们希望为每个项目的每个子项目组合添加一个注释，并为每个子项目添加一个布尔属性，因此最终如下所示：
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 ... (the rest of Item 1 possible combinations) 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
我尝试了内部联接和交叉联接的各种猫，但无法使其正常工作。我认为可以使用交叉连接将布尔部分添加到具有两行具有值True和False的表的表中，而且我还认为我需要执行“ FOR XML”子查询才能在一行中获得子项，但是我没有得到子项目的组合
这是我到目前为止的内容：
-- Schema creation and data filling DECLARE @Item TABLE (ItemId int, Name varchar(50)) DECLARE @Item_SubItem TABLE (ItemId int, SubitemId int) DECLARE @SubItem TABLE (SubitemId int, Name varchar(50)) INSERT INTO @Item values (1, 'Item 1') INSERT INTO @Item values (2, 'Item 2') INSERT INTO @Item values (3, 'Item 3') INSERT INTO @SubItem values (1, 'SubItem 1-1') INSERT INTO @SubItem values (2, 'SubItem 1-2') INSERT INTO @SubItem values (3, 'SubItem 1-3') INSERT INTO @SubItem values (4, 'SubItem 2-1') INSERT INTO @SubItem values (5, 'SubItem 3-1') INSERT INTO @SubItem values (6, 'SubItem 3-2') INSERT INTO @Item_SubItem values (1, 1) INSERT INTO @Item_SubItem values (1, 2) INSERT INTO @Item_SubItem values (1, 3) INSERT INTO @Item_SubItem values (2, 4) INSERT INTO @Item_SubItem values (3, 5) INSERT INTO @Item_SubItem values (3, 6) select I.Name, SI.Name from @Item I inner join @Item_SubItem ISI on ISI.ItemId = I.ItemId INNER JOIN @SubItem SI on SI.SubitemId = ISI.SubitemId order by I.Name, SI.Name -- Actual query SELECT ItemName = M.name, (SELECT iC.name + '=' + CASE AuxCode WHEN 1 THEN 'True' WHEN 0 THEN 'False' END + ' ' FROM Item_subitem AS iCGM INNER JOIN Subitem AS iC ON iC.SubitemId = iCGM.SubitemId CROSS JOIN (SELECT AuxCode = 1 UNION SELECT AuxCode = 0) Aux WHERE iCGM.ItemId = M.ItemId ORDER BY iC.name FOR XML PATH('')) FROM Item M
因此，对我来说失败的是子查询。任何帮助将非常感激！
这是达到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但错误值排序较高），这似乎可以更好地进行优化：
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));
您可能应该根据实际数据/架构测试这两种变体。
