問題描述

我有一個包含 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.

這篇關于t-sql :與父母，孩子，孫子...... n 個孩子一起獲得一行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！