Résumé récursif de la structure de l'arbre
j'ai une struture d'arbre dans une seule table. La table est un arbre de catégories qui peuvent être imbriquées sans fin. Chaque catégorie a une colonne ProductCount qui indique combien de produits sont directement dans la catégorie (ne pas sommer les catégories enfant).
Id | ParentId | Name | ProductCount
------------------------------------
1 | -1 | Cars | 0
2 | -1 | Bikes | 1
3 | 1 | Ford | 10
4 | 3 | Mustang | 7
5 | 3 | Focus | 4
je voudrais faire une requête sql pour chaque ligne/catégorie me donne le nombre de produits, y compris ceux dans les catégories enfants.
la sortie du tableau ci-dessus devrait être
Id | ParentId | Name | ProductCount | ProductCountIncludingChildren
--------------------------------------------------------------------------
1 | -1 | Cars | 0 | 21
2 | -1 | Bikes | 1 | 1
3 | 1 | Ford | 10 | 21
4 | 3 | Mustang | 7 | 7
5 | 3 | Focus | 4 | 4
je je sais que je devrais probablement utiliser CTE, mais je ne peux pas tout à fait le faire fonctionner comme il le devrait.
toute aide est appréciée!
5 réponses
vous pouvez utiliser un CTE récursif où vous dans la partie ancre obtenez toutes les lignes et dans la partie récursive joindre pour obtenir les lignes d'enfant. Rappelez-vous l'original Id
alias RootID
à partir de l'ancrage de part et d'agrégation sum dans la requête principale regroupés par RootID
.
MS SQL Server 2012 Schema Setup:
create table T
(
Id int primary key,
ParentId int,
Name varchar(10),
ProductCount int
);
insert into T values
(1, -1, 'Cars', 0),
(2, -1, 'Bikes', 1),
(3, 1, 'Ford', 10),
(4, 3, 'Mustang', 7),
(5, 3, 'Focus', 4);
create index IX_T_ParentID on T(ParentID) include(ProductCount, Id);
Requête 1:
with C as
(
select T.Id,
T.ProductCount,
T.Id as RootID
from T
union all
select T.Id,
T.ProductCount,
C.RootID
from T
inner join C
on T.ParentId = C.Id
)
select T.Id,
T.ParentId,
T.Name,
T.ProductCount,
S.ProductCountIncludingChildren
from T
inner join (
select RootID,
sum(ProductCount) as ProductCountIncludingChildren
from C
group by RootID
) as S
on T.Id = S.RootID
order by T.Id
option (maxrecursion 0)
| ID | PARENTID | NAME | PRODUCTCOUNT | PRODUCTCOUNTINCLUDINGCHILDREN |
|----|----------|---------|--------------|-------------------------------|
| 1 | -1 | Cars | 0 | 21 |
| 2 | -1 | Bikes | 1 | 1 |
| 3 | 1 | Ford | 10 | 21 |
| 4 | 3 | Mustang | 7 | 7 |
| 5 | 3 | Focus | 4 | 4 |
c'est le même concept que la réponse de Tom, mais moins de code (et beaucoup plus rapide).
with cte as
(
select v.Id, v.ParentId, v.Name, v.ProductCount,
cast('/' + cast(v.Id as varchar) + '/' as varchar) Node
from Vehicle v
where ParentId = -1
union all
select v.Id, v.ParentId, v.Name, v.ProductCount,
cast(c.Node + CAST(v.Id as varchar) + '/' as varchar)
from Vehicle v
join cte c on v.ParentId = c.Id
)
select c1.Id, c1.ParentId, c1.Name, c1.ProductCount,
c1.ProductCount + SUM(isnull(c2.ProductCount, 0)) ProductCountIncludingChildren
from cte c1
left outer join cte c2 on c1.Node <> c2.Node and left(c2.Node, LEN(c1.Node)) = c1.Node
group by c1.Id, c1.ParentId, c1.Name, c1.ProductCount
order by c1.Id
SQL Fiddle (j'ai ajouté quelques lignes de données supplémentaires pour tester)
en Fait, cela pourrait être une bonne utilisation de HIERARCHYID dans SQL Server..
CREATE TABLE [dbo].[CategoryTree]
(
[Id] INT,
[ParentId] INT,
[Name] VARCHAR(100),
[ProductCount] INT
)
GO
INSERT [dbo].[CategoryTree]
VALUES
(1, -1, 'Cars', 0),
(2, -1, 'Bikes', 1),
(3, 1, 'Ford', 10),
(4, 3, 'Mustang', 7),
(5, 3, 'Focus', 4)
--,(6, 1, 'BMW', 100)
GO
Requête
WITH [cteRN] AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY [ParentId] ORDER BY [ParentId]) AS [ROW_NUMBER]
FROM [dbo].[CategoryTree]
),
[cteHierarchy] AS (
SELECT CAST(
CAST(hierarchyid::GetRoot() AS VARCHAR(100))
+ CAST([ROW_NUMBER] AS VARCHAR(100))
+ '/' AS HIERARCHYID
) AS [Node],
*
FROM [cteRN]
WHERE [ParentId] = -1
UNION ALL
SELECT CAST(
hierarchy.Node.ToString()
+ CAST(RN.[ROW_NUMBER] AS VARCHAR(100)
) + '/' AS HIERARCHYID),
rn.*
FROM [cteRN] rn
INNER JOIN [cteHierarchy] hierarchy
ON rn.[ParentId] = hierarchy.[Id]
)
SELECT x.[Node].ToString() AS [Node],
x.[Id], x.[ParentId], x.[Name], x.[ProductCount],
x.[ProductCount] + SUM(ISNULL(child.[ProductCount],0))
AS [ProductCountIncludingChildren]
FROM [cteHierarchy] x
LEFT JOIN [cteHierarchy] child
ON child.[Node].IsDescendantOf(x.[Node]) = 1
AND child.[Node] <> x.[Node]
GROUP BY x.[Node], x.[Id], x.[ParentId], x.[Name], x.[ProductCount]
ORDER BY x.[Id]
Résultat
Je n'ai pas pu trouver une bonne réponse T-SQL, basée sur la configuration, mais j'ai trouvé une réponse: La table temporaire imite la structure de votre table. La variable table est une table de travail.
--Initial table
CREATE TABLE #products (Id INT, ParentId INT, NAME VARCHAR(255), ProductCount INT)
INSERT INTO #products
( ID,ParentId, NAME, ProductCount )
VALUES ( 1,-1,'Cars',0),(2,-1,'Bikes',1),(3,1,'Ford',10),(4,3,'Mustang',7),(5,3,'Focus',4)
--Work table
DECLARE @products TABLE (ID INT, ParentId INT, NAME VARCHAR(255), ProductCount INT, ProductCountIncludingChildren INT)
INSERT INTO @products
( ID ,
ParentId ,
NAME ,
ProductCount ,
ProductCountIncludingChildren
)
SELECT Id ,
ParentId ,
NAME ,
ProductCount,
0
FROM #products
DECLARE @i INT
SELECT @i = MAX(id) FROM @products
--Stupid loop - loops suck
WHILE @i > 0
BEGIN
WITH cte AS (SELECT ParentId, SUM(ProductCountIncludingChildren) AS ProductCountIncludingChildren FROM @products GROUP BY ParentId)
UPDATE p1
SET p1.ProductCountIncludingChildren = p1.ProductCount + isnull(p2.ProductCountIncludingChildren,0)
FROM @products p1
LEFT OUTER JOIN cte p2 ON p1.ID = p2.ParentId
WHERE p1.ID = @i
SELECT @i = @i - 1
END
SELECT *
FROM @products
DROP TABLE #products
je serais très intéressé de voir une meilleure approche, basée sur des ensembles. Le problème que j'ai rencontré, c'est que lorsque vous utilisez des cte récursifs, vous commencez avec le parent et travaillez avec les enfants - cela ne fonctionne pas vraiment pour obtenir une somme au niveau des parents. Vous auriez à faire une sorte de arrière expression de table commune récursive.
ce ne sera pas optimal mais cela fonctionne, cependant cela implique 2 CTEs. 1 CTE principal et un CTE dans une fonction de valeur de table pour résumer les valeurs pour chaque sous-arbre.
La première CTE
;WITH cte
AS
(
SELECT
anchor.Id,
anchor.ParentId,
anchor.Name,
anchor.ProductCount,
s.Total AS ProductCountIncludingChildren
FROM
testTable anchor
CROSS APPLY SumChild(anchor.id) s
WHERE anchor.parentid = -1
UNION ALL
SELECT
child.Id,
child.ParentId,
child.Name,
child.ProductCount,
s.Total AS ProductCountIncludingChildren
FROM
cte
INNER JOIN testTable child on child.parentid = cte.id
CROSS APPLY SumChild(child.id) s
)
SELECT * from cte
ET la fonction
CREATE FUNCTION SumChild
(
@id int
)
RETURNS TABLE
AS
RETURN
(
WITH cte
AS
(
SELECT
anchor.Id,
anchor.ParentId,
anchor.ProductCount
FROM
testTable anchor
WHERE anchor.id = @id
UNION ALL
SELECT
child.Id,
child.ParentId,
child.ProductCount
FROM
cte
INNER JOIN testTable child on child.parentid = cte.id
)
SELECT SUM(ProductCount) AS Total from CTE
)
GO
ce qui donne:
de la table source
mes excuses pour le formatage.