sql返回嵌套结果集？
是否可以让单个 sql 查询返回嵌套结构，而不是进行递归数据库调用来构建数组或对象？
我正在使用类似于此伪代码的东西来构建：
parentCategory = 'SELECT *
FROM Category
WHERE child_category IS NULL
AND ParentIDNo IS NULL';
while parentCategory do
childCategory = 'SELECT *
FROM Category
WHERE parent_id = parentCategory.id';
if (parentCategory.id)
do recursive 'SELECT *
FROM Category
WHERE parent_id = parentCategory.id';
end
Cat_1
-child_1
-child_2
--grandchild_1
Cat_2
-child_1
-child_2
--grandchild_1
Is it possible to have a single sql query return a nested structure rather than doing recursive db calls to build up the array or object?
I'm using something similar to this pseudo code to build up :
parentCategory = 'SELECT *
FROM Category
WHERE child_category IS NULL
AND ParentIDNo IS NULL';
while parentCategory do
childCategory = 'SELECT *
FROM Category
WHERE parent_id = parentCategory.id';
if (parentCategory.id)
do recursive 'SELECT *
FROM Category
WHERE parent_id = parentCategory.id';
end
Cat_1
-child_1
-child_2
--grandchild_1
Cat_2
-child_1
-child_2
--grandchild_1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查看递归 ctes 假定 sql 2005 或更高版本
Check out recursive ctes assumes sql 2005 or later
如果您修改表以包含到树顶部的分隔路径，则不必递归地执行此操作。
例如，如果树的 grandchild_1 节点的树路径为“cat_1|child_2|grandchild_1”，那么您将能够拆分字符串以加载到树中，并能够确定您在树中的级别是在。此外，当您从表中进行选择时，您将能够按路径进行排序，并且树将完全按照您想要绘制的方式出现。
缺点是您必须在对树进行任何更改时维护此路径。
If you modify your table to contain a delimited path to the top of the tree then you don't have to do this recursively.
For example if you had the tree path be "cat_1|child_2|grandchild_1" for the grandchild_1 node of the tree then you would be able to split the string for loading into a tree as well as be able to determine the level in the tree you were at. Also when you select from the table you would be able to order by the path and the tree would come out exactly as you wanted to draw it.
the negative is that you would have to maintain this path on any changes to the tree.
如果您使用 SQL 2005 及更高版本，这是很有可能的。有关分层查询的入门知识，请参阅：http://www. ienablemuch.com/2010/04/simple-hierarchical-query-display.html
That's very possible if you are using SQL 2005 and up. For a primer on hierarchical query see this: http://www.ienablemuch.com/2010/04/simple-hierarchical-query-display.html