执行递归SQL查询(SQL Server 2005中)
我有这个表:执行递归SQL查询(SQL Server 2005中)
ID_Articulo ID_Componente Tipo Cantidad
1 5 2 1.5
5 3 1 3
1 6 1 6
2 3 1 3.5
6 8 2 4.2
8 9 1 2
我需要找到Cantidad字段的总和对于一个给定ID_Articulo和TIPO = 1。例如,对于ID_Articulo = 1应该是1.5 * 3 + 6 + 6 * 4.2 * 2 = 60.9。
每个ID_Articulo的级别(深度)数量是可变的。
这可以用SQL查询吗?我的数据库是SQL Server 2005中
aditional的信息
场ID_Articulo和ID_Compenente都与同一个表Articulos。这就是为什么数据是递归的。因此,对于给定的例子,我有:
ReC#1 is not Tipo=1, but relates ID 1 with ID 5
ReC#2 relates ID 5 with ID 3 and Tipo=1 So, I have 1.5 (of ReC#1) * 3
ReC#3 relates ID 1 with ID 6 and Tipo=1, so i have 6
ReC#4 is from another ID
ReC#5 relates ID 6 with ID 8 but Tipo!=1 so i don't sum
ReC#6 relates ID 8 and ID 9 and Tipo=1, so I have 6 * 4.2 * 2
最终解决
这是最后的代码(有些字段名称不同):
;WITH CTE AS (
SELECT b.ID_Articulo, ID_Componente, Tipo, Cantidad,
CAST(Cantidad AS DECIMAL(6,2)) AS partialSum
FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente
WHERE b.ID_Articulo = 2716
UNION ALL
SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
CAST(c.partialSum * t.Cantidad AS DECIMAL(6,2)) AS partialSum
FROM (SELECT b.ID_Articulo, ID_Componente, A.Tipo, Cantidad FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente inner join Articulos a2 on a2.ID_Articulo=b.ID_Articulo where a2.Tipo<>'I') as t
INNER JOIN CTE AS c ON c.ID_Componente = t.ID_Articulo
)
SELECT SUM(partialSum)
FROM CTE
WHERE Tipo = 'I'
感谢@ giorgos,betsos为最终解决方案
您可以使用以下递归CTE来获得预期结果:
;WITH CTE AS (
SELECT ID_Articulo, ID_Componente, Tipo, Cantidad,
CAST(Cantidad AS DECIMAL(6,1)) AS partialSum
FROM mytable
WHERE ID_Articulo = 1
UNION ALL
SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
CAST(c.partialSum * t.Cantidad AS DECIMAL(6,1)) AS partialSum
FROM mytable AS t
INNER JOIN CTE AS c ON t.ID_Articulo = c.ID_Componente
)
SELECT SUM(partialSum)
FROM CTE
WHERE Tipo = 1
什么上述递归呢,是返回从ID_Articulo = 1
发出所有分支,与Cantidad
值相乘的累积产物沿着:
ID_Articulo ID_Componente Tipo Cantidad partialSum
------------------------------------------------------------------
1 5 2 1.5 1.5
1 6 1 6.0 6.0
6 8 2 4.2 25.2
8 9 1 2.0 50.4
5 3 1 3.0 4.5
结果是使用SUM
上partialSum
计算只有对于那些行Tipo = 1
。
你是男人!如何猜测需要什么? –
@GiorgiNakeuri我只是疯狂猜测。只有OP可以验证上述解决方案是否是他/她的真实意图! –
非常接近!我用真实的数据评估这个数据并告诉你它是怎么回事! – ericpap
我不看你怎么得到这个总和ID_Articulo = 1,您所提供的样品台。我也不明白为什么这是递归的? – Paddy
对不起。我的错。我编辑问题以提供更多信息。 – ericpap
是的,为什么你的公式的1.5 * 3部分?第一行中只有1.5,其中'tipo'是2,所以根据描述,不应该包含它。你的例子只是让你的问题不太清楚。 –