問題描述
我有一個包含 2 列的表格:ID, ParentID
I have a table with 2 columns: ID, ParentID
我想創(chuàng)建一個新表/視圖，每一行都包含所有級別的所有孩子到某個父母...
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 的函數(shù)，它返回我在最后一次選擇中得到的行，所以也許我可以在 while 循環(huán)或其他東西中使用它...
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...
問題是我怎么知道一開始要構(gòu)造多少列?知道如何參加這個挑戰(zhàn)嗎?
the question is how can i know how many columns to construct at the beginning ? any idea how to access this challenge ?
推薦答案
你想要得到的是一個數(shù)據(jù)透視表，但在我看來，這個任務(wù)不屬于數(shù)據(jù)庫.我寧愿得到一個包含行的結(jié)果集并從外部"旋轉(zhuǎn)它們.
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".
為了實現(xiàn)我所描述的，在 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.
注意:雖然你寫了沒有遞歸所以沒有機會堆棧溢出"，你仍然需要保護自己免受無限循環(huán)的影響.我個人使用遞歸并為其建立最大深度"；當遞歸嘗試比限制更深"時，我停止它并返回錯誤.
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.
這篇關(guān)于t-sql :與父母，孩子，孫子...... n 個孩子一起獲得一行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！