我正在try 从每个父母那里获取所有子元素的计数(其中子子元素是重要的).
这是示例数据库(MSSQL).
INSERT INTO NODES VALUES(NULL, 1);
INSERT INTO NODES VALUES(NULL, 2);
INSERT INTO NODES VALUES(2, 3);
INSERT INTO NODES VALUES(1, 4);
INSERT INTO NODES VALUES(1, 5);
INSERT INTO NODES VALUES(3, 6);
INSERT INTO NODES VALUES(NULL, 7);
INSERT INTO NODES VALUES(NULL, 8);
INSERT INTO NODES VALUES(8, 9);
INSERT INTO NODES VALUES(7, 10);
INSERT INTO NODES VALUES(9, 11);
INSERT INTO NODES VALUES(11, 12);
INSERT INTO NODES VALUES(10, 13);
INSERT INTO NODES VALUES(10, 14);
INSERT INTO NODES VALUES(4, 15);
层级 struct 在哪里;
- 1
- 4
- 15
- 5
- 2
- 3
- 6
- 7
- 10
- 13
- 14
- 8
- 9
- 11
- 12
而预期的结果是:
id | Children Count |
---|---|
1 | 3 |
4 | 1 |
5 | 0 |
2 | 2 |
3 | 1 |
6 | 0 |
7 | 3 |
10 | 2 |
13 | 1 |
14 | 0 |
8 | 3 |
9 | 2 |
11 | 1 |
12 | 0 |
每次我制定策略来制定查询时,我都会达到必须在运行时迭代由查询形成的表的地步.
如果我对结果进行更深入的分组(使用父代ID),我可以只生成子代(不包括子代)的计数,这样我们就可以累加到根.但很明显,我需要迭代我在查询中形成的表,我不知道递归是否正确.
为了更好地表达自己,我会用我已经达到的部分和我想要做的事情来展示它;
WITH tree AS
(
SELECT n1.parentid, n1.id, 1 AS level
FROM NODES AS n1
WHERE n1.parentid IS NULL
UNION ALL
SELECT n2.parentid, n2.id, level + 1 AS level
FROM NODES AS n2
INNER JOIN tree ON n2.parentid = tree.id
), levels AS
(
SELECT *
FROM tree
)
SELECT parentid, id, (COUNT(*) OVER(PARTITION BY parentid ORDER BY parentid)) AS childrencountofparentid,
ROW_NUMBER() OVER(ORDER BY parentid DESC) AS rownumber
FROM levels
其中,输出为:
parentid | id | childrencountofparentid | rownumber |
---|---|---|---|
11 | 12 | 1 | 1 |
10 | 13 | 2 | 2 |
10 | 14 | 2 | 3 |
9 | 11 | 1 | 4 |
8 | 9 | 1 | 5 |
7 | 10 | 1 | 6 |
4 | 15 | 1 | 7 |
3 | 6 | 1 | 8 |
2 | 3 | 1 | 9 |
1 | 4 | 2 | 10 |
1 | 5 | 2 | 11 |
null | 1 | 4 | 12 |
null | 2 | 4 | 13 |
null | 7 | 4 | 14 |
null | 8 | 4 | 15 |
I want to do this: Full Image
我想使用前面行的结果,类似于LAG,但我必须迭代所有前面的行.