創建多個級別的層次結構，其中每個節點都有隨機數量的子節點

January 15, 2016

我需要創建一些涉及層次結構的測試數據。我可以讓它變得簡單並做幾個CROSS JOINs，但這會給我一個完全統一/沒有任何變化的結構。這不僅看起來乏味，而且測試數據缺乏變化有時會掩蓋原本會發現的問題。所以，我想生成一個遵循這些規則的非統一層次結構：
3層深
級別 1 隨機 5 - 20 個節點
級別 2 是 1 - 10 個節點，每個級別 1 的每個節點都是隨機的
級別 3 是 1 - 5 個節點，每個級別 2 的每個節點都是隨機的
所有分支將有 3 層深。在這一點上，深度均勻性是可以的。
在任何給定級別上的子節點的名稱都可以有重疊（即，子節點的名稱不需要在同一級別的所有節點中都是唯一的）。
術語“隨機”在這裡定義為偽隨機，而不是唯一隨機。這需要提及，因為術語“隨機”通常用於表示“不產生重複的給定集合的隨機排序”。我接受隨機 = 隨機，並且如果級別 1 的每個節點的子節點數只有 4、7 和 8 個，即使級別 1 的 20 個節點可能每個節點有 1-10 個子節點，那很好，因為那是隨機的。
儘管這可以通過嵌套WHILE循環很容易地完成，但首選是找到基於集合的方法。一般來說，生成測試數據並不像生產程式碼那樣對效率有要求，但採用基於集合的方法可能更具教育意義，並且有助於在未來找到基於集合的方法來解決問題。因此WHILE不排除循環，但只能在不可能使用基於集合的方法時使用。
基於集合 = 理想情況下是單個查詢，無論 CTE、APPLY 等如何。因此使用現有或內聯數字表就可以了。使用 WHILE / CURSOR / 程序方法將不起作用。我想將部分數據暫存到臨時表或表變數中很好，只要操作都是基於集合的，沒有循環。但是，話雖如此，單查詢方法可能會比多個查詢更受青睞，除非可以證明多查詢方法實際上更好。還請記住，什麼構成“更好”通常是主觀的；-)。還請記住，前一句中“典型地”的使用也是主觀的。
SQL Server 的任何版本（我想是 2005 和更新版本）都可以。
只有純 T-SQL：沒有那些愚蠢的 SQLCLR 東西！！至少在生成數據方面。將使用 SQLCLR 創建目錄和文件。但在這裡，我只專注於創造要創造的價值。
T-SQL 多語句 TVF 被認為是過程的，而不是基於集合的，即使在外部它們掩蓋了集合中的過程方法。有時這是絕對合適的。這不是其中之一。同樣，T-SQL 標量函式也是不允許的，不僅因為它們也是過程性的，而且查詢優化器有時會記憶體它們的值並重複它，從而導致輸出不符合預期。
T-SQL 內聯 TVF（又名 iTVF）是 okey-dokey，因為它們是基於集合的，並且實際上與 using 相同[ CROSS | OUTER ] APPLY，這在上面說明是可以的。
查詢的重複執行應產生與先前執行大不相同的結果。
**澄清更新 1：**最終結果集應表示為 Level3 的每個不同節點都有一行，完整路徑從 Level1 開始。這意味著 Level1 和 Level2 值必然會在一行或多行中重複，除非只有一個 Level2 節點包含一個 Level3 節點。
**澄清更新 2：**對於具有名稱或標籤的每個節點，而不僅僅是一個數字，存在非常強烈的偏好。這將使生成的測試數據更有意義和更現實。
我不確定這些附加資訊是否重要，但以防萬一它有助於了解一些上下文，測試數據與我對這個問題的回答有關：
將 XML 文件導入 SQL Server 2012
雖然此時不相關，但生成此層次結構的最終目標是創建一個目錄結構來測試遞歸文件系統方法。級別 1 和 2 將是目錄，而級別 3 將最終成為文件名。我已經搜尋了（在這里和通過Google）並且只找到了一個生成隨機層次結構的參考：
Linux：創建隨機目錄/文件層次結構
這個問題（在 StackOverflow 上）實際上與期望的結果非常接近，因為它也試圖創建一個用於測試的目錄結構。但這個問題（和答案）集中在 Linux/Unix shell 腳本，而不是我們生活的基於集合的世界。
現在，我知道如何生成隨機數據，並且已經在這樣做以創建文件的內容，以便它們也可以顯示變化。這裡棘手的部分是每個集合中的元素數量是隨機的，而不是特定的欄位。並且，每個節點內的元素數量需要與同一級別上的其他節點隨機。
範例層次結構
    Level 1
             Level 3
|---- A
|     |-- 1
|     |   |--- I
|     |
|     |-- 2
|         |--- III
|         |--- VI
|         |--- VII
|         |--- IX
|
|---- B
|     |-- 87
|         |--- AAA
|         |--- DDD
|
|---- C
     |-- ASDF
     |   |--- 11
     |   |--- 22
     |   |--- 33
     |
     |-- QWERTY
     |   |--- beft
     |
     |-- ROYGBP
         |--- Poi
         |--- Moi
         |--- Soy
         |--- Joy
         |--- Roy
描述上述層次結構的範例結果集
Level 1    Level 2    Level 3
A          1          I
A          2          III
A          2          VI
A          2          VII
A          2          IX
B          87         AAA
B          87         DDD
C          ASDF       11
C          ASDF       22
C          ASDF       33
C          QWERTY     beft
C          ROYGBP     Poi
C          ROYGBP     Moi
C          ROYGBP     Soy
C          ROYGBP     Joy
C          ROYGBP     Roy

（**OP的註釋：**首選解決方案是第4個/最後一個程式碼塊）

在我看來，XML 顯然是這裡使用的資料結構的明顯選擇。

with N as
(
 select T.N
 from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
             (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)

select top(5 + abs(checksum(newid())) % 15)
 N1.N as '@Value',
 (
 select top(1 + abs(checksum(newid())) % 10)
   N2.N as '@Value',
   (
   select top(1 + abs(checksum(newid())) % 5)
     N3.N as '@Value'
   from N as N3
   where N2.N &gt; 0
   for xml path('Level3'), type
   )
 from N as N2
 where N1.N &gt; 0
 for xml path('Level2'), type
 )
from N as N1
for xml path('Level1'), root('Root');

使 SQL Servertop()為每個節點使用不同值的技巧是使子查詢相關聯。N1.N > 0和 N2.N > 0。

扁平化 XML：

declare @X xml;

with N as
(
 select T.N
 from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
             (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select @X  = (
            select top(5 + abs(checksum(newid())) % 15)
              N1.N as '@Value',
              (
              select top(1 + abs(checksum(newid())) % 10)
                N2.N as '@Value',
                (
                select top(1 + abs(checksum(newid())) % 5)
                  N3.N as '@Value'
                from N as N3
                where N2.N &gt; 0
                for xml path('Level3'), type
                )
              from N as N2
              where N1.N &gt; 0
              for xml path('Level2'), type
              )
            from N as N1
            for xml path('Level1')
            );


select L1.X.value('@Value', 'varchar(10)')+'\'+
      L2.X.value('@Value', 'varchar(10)')+'\'+
      L3.X.value('@Value', 'varchar(10)')
from @X.nodes('/Level1') as L1(X)
 cross apply L1.X.nodes('Level2') as L2(X)
 cross apply L2.X.nodes('Level3') as L3(X);

還有一個完全沒有 XML 的版本。

with N as
(
 select T.N
 from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
             (12),(13),(14),(15),(16),(17),(18),(19),(20)) as T(N)
)
select cast(N1.N as varchar(10))+'\'+
      cast(N2.N as varchar(10))+'\'+
      cast(N3.N as varchar(10))
from (
    select top(5 + abs(checksum(newid())) % 15)
      N.N
    from N
    ) as N1
 cross apply
    (
    select top(1 + abs(checksum(newid())) % 10)
      N.N
    from N
    where N1.N &gt; 0
    ) as N2
 cross apply
    (
    select top(1 + abs(checksum(newid())) % 5)
      N.N
    from N
    where N2.N &gt; 0
    ) as N3;

相關性N1.N > 0仍然 N2.N > 0很重要。

使用具有 20 個名稱的表的版本，而不僅僅是整數。

declare @Elements table
(
 Name nvarchar(50) not null
);

insert into @Elements(Name)
select top(20) C.name 
from sys.columns as C
group by C.name;

select N1.Name + N'\' + N2.Name + N'\' + N3.Name
from (
    select top(5 + abs(checksum(newid())) % 15)
      E.Name
    from @Elements as E
    ) as N1
 cross apply
    (
    select top(1 + abs(checksum(newid())) % 10)
      E.Name
    from @Elements as E
    where N1.Name &gt; ''
    ) as N2
 cross apply
    (
    select top(1 + abs(checksum(newid())) % 5)
      E.Name
    from @Elements as E
    where N2.Name &gt; ''
    ) as N3;

引用自：https://dba.stackexchange.com/questions/117469

創建多個級別的層次結構，其中每個節點都有隨機數量的子節點

相關問答

使用者定義函式中的 PWDENCRYPT

從 SQL 表中獲取嵌套/分層數據

在有序的行列表中轉換層次結構

為樹中的每個節點創建層次鏈（反向路徑）

HierarchyID：獲取父母列表的所有後代

選擇組合 SELECT DISTINCT 的最新單個欄位？