gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 04:42:44 26 4
gpt4 key购买 nike

我在这里看到过类似的问题,但要么我没有得到答案,要么它们不适用......这是我需要的,并且认为它非常简单:
我有一组项目,每个项目都有一组子项目。每个项目的子项目数会发生变化。例如:

Item 1
SubItem 1-1
SubItem 1-2
SubItem 1-3
Item 2
SubItem 2-1
Item 3
SubItem 3-1
SubItem 3-2

对于一个非常具体的用途,想要为每个项目的每个可能的子项目组合添加注释,并在每个子项目上添加一个 bool 属性,所以它最终是这样的:
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

我尝试了各种内部连接和交叉连接,但无法使其工作。我认为可以使用交叉连接将 bool 部分添加到具有值 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 排序,但假值排序更高):
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));

您可能应该针对您的实际数据/模式测试这两种变体。

关于sql - 使用 SQL Server 创建所有可能的组合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18658631/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com