t-sql :与父母，孩子，孙子...... n 个孩子一起获得一行
我有一个包含 2 列的表格:ID, ParentID
I have a table with 2 columns: ID, ParentID
我想创建一个新表/视图，每一行都包含所有级别的所有孩子到某个父母...
i want to make a new table/view that each row includes all the children in all levels to a certain parents...
例如:
ParentID Level_1 Level_2 Level_3 ... Level_n
这意味着 parentID 是 Level_1 的父级，它是 level_3 的父级 2 的父级，依此类推...
it means that parentID is the parent of Level_1 which is the Parent of level 2 which is the parent of level_3 and so on...
我需要做没有递归所以没有机会堆栈溢出...
i need to do it with no recursion so no chance to stack overflow...
在 T-SQL 中有一个名为 @@rowcount 的函数，它返回我在最后一次选择中得到的行，所以也许我可以在 while 循环或其他东西中使用它...
in T-SQL there's a function called @@rowcount which returns the rows i got in the last select so maybe i can use it inside a while loop or something...
问题是我怎么知道一开始要构造多少列?知道如何参加这个挑战吗?
the question is how can i know how many columns to construct at the beginning ? any idea how to access this challenge ?
推荐答案
你想要得到的是一个数据透视表，但在我看来，这个任务不属于数据库.我宁愿得到一个包含行的结果集并从外部"旋转它们.
What you're trying to get is a pivot table, but, in my opinion, this task doesn't belong to the database. I'd rather get a result set with rows and pivot them "externally".
为了实现我所描述的，在 SQL Server 2005+ 中，您可以使用公共表表达式 (在这里你可以找到一个例子，而 SQL Server 2000 需要一个稍微不同的方法.
To achieve what I described, in SQL Server 2005+ you can use a Common Table Expression (here you can find an example, while SQL Server 2000 requires a slightly different approach.
注意:虽然你写了没有递归所以没有机会堆栈溢出"，你仍然需要保护自己免受无限循环的影响.我个人使用递归并为其建立最大深度"；当递归尝试比限制更深"时，我停止它并返回错误.
Note: although you wrote "no recursion so no chance to stack overflow", you'll still have to proect yourself from infinite loops. I personally use recursion and establish a maximum "depth" for it; when the recursion attempts to go "deeper" than the limit, I stop it and return an error.
相关文章