如何进行多级嵌套循环加入？
我敢肯定，这一定是一个常见的编码问题，但我什至不知道该怎么称呼。
对于任何给定的人，我想找到他们的团队 - 现在可能有团队内部的团队，或者可能没有 - 这是无法预测的。
因此，我可以做嵌套的循环，但是我想知道是否有更聪明的方式做到这一点？我最终会一遍又一遍地迭代相同的数据集（大约10,000个记录，大概是7个层次结构），这不是最有效的。
MS SQL Server 2012？ Python 3.10？
| Persyid | PersonId | lev3 | LEV2 LEV2 | LEV1 | ManagerID |
|---|---|---|---|---|---|
| 4703 | John | Department A | 组D | 级A | 5763 |
| 4367 | JANET | 部门B | 组E | 部B | 4744 |
| 7033 | BOB | 部门C | 组D | 部门A | 5763 5763 5763 |
| 5763 | SARAH | NONE NONE GROBEN NONE | D GROUP D部门D | 部门A | 52224 5224 |
| 5224 52224 | B pHIL | NONE BLEN NONE | 5224 NONE GROUM NONE GROUM | ENOME B | 5224 52224 |
| 5224 522224 | 佩特拉 | 无 | 无， | 没有 | 一个 |
让我想找到佩特拉的团队-ID 5224-应该是每个人，因为她是食物链的顶级。但是菲尔呢？ ID 4744-那只是珍妮特。与莎拉（Sarah）-ID 5763相比，她的团队中都有约翰和鲍勃。
该SQL代码只能确定直接团队，而不是团队中的团队。
SELECT
M.[Employee Name], S.[Employee Name]
FROM [Staff] M
JOIN [Staff] S
ON M.PositionID = S.ManagerPosition
WHERE
M.PositionID > 0
ORDER BY
m.[Employee Name]
我怎么能在嵌套环内使用嵌套环的嵌套环 - 深7个级别？
I'm sure this must be a common coding question, but I don't even know what to call it.
For any given Person, I want to find their team - now there may be teams inside of teams, or maybe there are not - it's not predictable.
So I can just do nested loops which is fine, but I'm wondering if there is smarter fancier way of doing it? I'll just end up iterating over the same dataset (about 10,000 records, representing maybe 7 levels of hierarchy) over and over again, which isn't the most efficient.
MS SQL Server 2012? Python 3.10?
| PersonID | PersonName | Lev3 | Lev2 | Lev1 | ManagerID |
|---|---|---|---|---|---|
| 4703 | John | Department A | Group D | Division A | 5763 |
| 4367 | Janet | Department B | Group E | Division B | 4744 |
| 7033 | Bob | Department C | Group D | Division A | 5763 |
| 5763 | Sarah | None | Group D | Division A | 5224 |
| 4744 | Phil | None | Group E | Division B | 5224 |
| 5224 | Petra | None | None | None | None |
Let's say I want to find Petra's team - ID 5224 - it should be everybody, since she is top of the food chain. But Phil? ID 4744 - that's only Janet. Compared to Sarah - ID 5763 who has both John and Bob in her team.
This SQL code will identify only the immediate team - not the teams within teams.
SELECT
M.[Employee Name], S.[Employee Name]
FROM [Staff] M
JOIN [Staff] S
ON M.PositionID = S.ManagerPosition
WHERE
M.PositionID > 0
ORDER BY
m.[Employee Name]
How could I so a nested loop with a nested loop, within a nested loop - upto 7 levels deep?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您将需要使用递归CTE。阅读本文并查看是否可以将其应用于您的情况： https://blog.sqlauthority.com/2012/04/24/sql-sql-server-server-server-indrodto-to-hierarchical-to-hierarchical-query-query-query-using-a- using-a-a-a-a-a-a-recursive--a-recursive--recursive--recursive--a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a CTE-A-PRIMER/
You will need to use a recursive CTE. Read this and see if you can apply it to your case: https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/