SQL Server - 使用聚集索引时如何存储数据页

Question

Solomon Rutzky

Asked: 2015-10-09 11:47:48 +0800 CST2015-10-09 11:47:48 +0800 CST 2015-10-09 11:47:48 +0800 CST

创建多个级别的层次结构，其中每个节点都有随机数量的子节点

772

我需要创建一些涉及层次结构的测试数据。我可以让它变得简单并做几个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 将最终成为文件名。我已经搜索了（在这里和通过谷歌）并且只找到了一个生成随机层次结构的参考：

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

3 个回答

Voted

Mikael Eriksson · Answer 1 · 2015-10-10T00:06:44+08:00

^{（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 > 0
    for xml path('Level3'), type
    )
  from N as N2
  where N1.N > 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 > 0
                 for xml path('Level3'), type
                 )
               from N as N2
               where N1.N > 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 > 0
     ) as N2
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 5)
       N.N
     from N
     where N2.N > 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 > ''
     ) as N2
  cross apply
     (
     select top(1 + abs(checksum(newid())) % 5)
       E.Name
     from @Elements as E
     where N2.Name > ''
     ) as N3;

Vladimir Baranov · Answer 2 · 2015-10-11T03:25:05+08:00

That was interesting.

My aim was to generate given number of levels with random number of child rows per each level in a properly linked hierarchical structure. Once this structure is ready it is easy to add extra info into it like file and folder names.

So, I wanted to generate a classic table for storing a tree:

ID int NOT NULL
ParentID int NULL
Lvl int NOT NULL

Since we are dealing with recursion, recursive CTE seems a natural choice.

I will need a table of numbers. Numbers in the table should start from 1. There should be at least 20 numbers in the table: MAX(LvlMax).

CREATE TABLE [dbo].[Numbers](
    [Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED 
(
    [Number] ASC
));

INSERT INTO Numbers(Number)
SELECT TOP(1000)
    ROW_NUMBER() OVER(ORDER BY S.object_id)  AS Number
FROM
    sys.all_objects AS S
ORDER BY Number;

Parameters for data generation should be stored in a table:

DECLARE @Intervals TABLE (Lvl int, LvlMin int, LvlMax int);
INSERT INTO @Intervals (Lvl, LvlMin, LvlMax) VALUES
(1, 5, 20),
(2, 1, 10),
(3, 1, 5);

Note, that the query is pretty flexible and all parameters are separated into one place. You can add more levels if needed, just add an extra row of parameters.

To make such dynamic generation possible I had to remember the random number of rows for the next level, so I have an extra column ChildRowCount.

Generating unique IDs is also somewhat tricky. I hard-coded the limit of 100 child rows per 1 parent row to guarantee that IDs don't repeat. This is what that POWER(100, CTE.Lvl) is about. As a result there are large gaps in IDs. That number could be a MAX(LvlMax), but I put constant 100 in the query for simplicity. The number of levels is not hard-coded, but is determined by @Intervals.

This formula

CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5

generates a random floating point number in the range [0..1), which is then scaled to the required interval.

The query logic is simple. It is recursive. First step generates a set of rows of the first level. Number of rows is determined by random number in TOP. Also, for each row there is a separate random number of child rows stored in ChildRowCount.

Recursive part uses CROSS APPLY to generate given number of child rows per each parent row. I had to use WHERE Numbers.Number <= CTE.ChildRowCount instead of TOP(CTE.ChildRowCount), because TOP is not allowed in recursive part of CTE. Didn't know about this limitation of SQL Server before.

WHERE CTE.ChildRowCount IS NOT NULL stops the recursion.

SQL Fiddle

WITH
CTE
AS
(
    SELECT 
        TOP(CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 1)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            AS int))
        Numbers.Number AS ID
        ,NULL AS ParentID
        ,1 AS Lvl
        ,CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 2)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            AS int) AS ChildRowCount
    FROM Numbers
    ORDER BY Numbers.Number

    UNION ALL

    SELECT
        CA.Number + CTE.ID * POWER(100, CTE.Lvl) AS ID
        ,CTE.ID AS ParentID
        ,CTE.Lvl + 1 AS Lvl
        ,CA.ChildRowCount
    FROM
        CTE
        CROSS APPLY
        (
            SELECT
                Numbers.Number
                ,CAST(
                    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
                    (
                    1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                      - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    )
                    + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    AS int) AS ChildRowCount
            FROM Numbers
            WHERE Numbers.Number <= CTE.ChildRowCount
        ) AS CA
    WHERE
        CTE.ChildRowCount IS NOT NULL
)
SELECT *
FROM CTE
ORDER BY Lvl, ParentID, ID;

Result (there can be up to 20 + 20*10 + 200*5 = 1220 rows if you are lucky)

+---------+----------+-----+-------------------+
|   ID    | ParentID | Lvl | ChildRowCount     |
+---------+----------+-----+-------------------+
|       1 | NULL     |   1 | 3                 |
|       2 | NULL     |   1 | 1                 |
|       3 | NULL     |   1 | 6                 |
|       4 | NULL     |   1 | 5                 |
|       5 | NULL     |   1 | 3                 |
|       6 | NULL     |   1 | 7                 |
|       7 | NULL     |   1 | 1                 |
|       8 | NULL     |   1 | 6                 |
|     101 | 1        |   2 | 3                 |
|     102 | 1        |   2 | 5                 |
|     103 | 1        |   2 | 1                 |
|     201 | 2        |   2 | 5                 |
|     301 | 3        |   2 | 4                 |
|     302 | 3        |   2 | 5                 |
|     303 | 3        |   2 | 1                 |
|     304 | 3        |   2 | 2                 |
|     305 | 3        |   2 | 4                 |
|     306 | 3        |   2 | 3                 |
|     401 | 4        |   2 | 3                 |
|     402 | 4        |   2 | 1                 |
|     403 | 4        |   2 | 2                 |
|     404 | 4        |   2 | 2                 |
|     405 | 4        |   2 | 4                 |
|     501 | 5        |   2 | 1                 |
|     502 | 5        |   2 | 3                 |
|     503 | 5        |   2 | 5                 |
|     601 | 6        |   2 | 2                 |
|     602 | 6        |   2 | 5                 |
|     603 | 6        |   2 | 3                 |
|     604 | 6        |   2 | 3                 |
|     605 | 6        |   2 | 4                 |
|     606 | 6        |   2 | 5                 |
|     607 | 6        |   2 | 4                 |
|     701 | 7        |   2 | 2                 |
|     801 | 8        |   2 | 2                 |
|     802 | 8        |   2 | 3                 |
|     803 | 8        |   2 | 3                 |
|     804 | 8        |   2 | 3                 |
|     805 | 8        |   2 | 5                 |
|     806 | 8        |   2 | 2                 |
| 1010001 | 101      |   3 | NULL              |
| 1010002 | 101      |   3 | NULL              |
| 1010003 | 101      |   3 | NULL              |
| 1020001 | 102      |   3 | NULL              |
| 1020002 | 102      |   3 | NULL              |
| 1020003 | 102      |   3 | NULL              |
| 1020004 | 102      |   3 | NULL              |
| 1020005 | 102      |   3 | NULL              |
| 1030001 | 103      |   3 | NULL              |
| 2010001 | 201      |   3 | NULL              |
| 2010002 | 201      |   3 | NULL              |
| 2010003 | 201      |   3 | NULL              |
| 2010004 | 201      |   3 | NULL              |
| 2010005 | 201      |   3 | NULL              |
| 3010001 | 301      |   3 | NULL              |
| 3010002 | 301      |   3 | NULL              |
| 3010003 | 301      |   3 | NULL              |
| 3010004 | 301      |   3 | NULL              |
| 3020001 | 302      |   3 | NULL              |
| 3020002 | 302      |   3 | NULL              |
| 3020003 | 302      |   3 | NULL              |
| 3020004 | 302      |   3 | NULL              |
| 3020005 | 302      |   3 | NULL              |
| 3030001 | 303      |   3 | NULL              |
| 3040001 | 304      |   3 | NULL              |
| 3040002 | 304      |   3 | NULL              |
| 3050001 | 305      |   3 | NULL              |
| 3050002 | 305      |   3 | NULL              |
| 3050003 | 305      |   3 | NULL              |
| 3050004 | 305      |   3 | NULL              |
| 3060001 | 306      |   3 | NULL              |
| 3060002 | 306      |   3 | NULL              |
| 3060003 | 306      |   3 | NULL              |
| 4010001 | 401      |   3 | NULL              |
| 4010002 | 401      |   3 | NULL              |
| 4010003 | 401      |   3 | NULL              |
| 4020001 | 402      |   3 | NULL              |
| 4030001 | 403      |   3 | NULL              |
| 4030002 | 403      |   3 | NULL              |
| 4040001 | 404      |   3 | NULL              |
| 4040002 | 404      |   3 | NULL              |
| 4050001 | 405      |   3 | NULL              |
| 4050002 | 405      |   3 | NULL              |
| 4050003 | 405      |   3 | NULL              |
| 4050004 | 405      |   3 | NULL              |
| 5010001 | 501      |   3 | NULL              |
| 5020001 | 502      |   3 | NULL              |
| 5020002 | 502      |   3 | NULL              |
| 5020003 | 502      |   3 | NULL              |
| 5030001 | 503      |   3 | NULL              |
| 5030002 | 503      |   3 | NULL              |
| 5030003 | 503      |   3 | NULL              |
| 5030004 | 503      |   3 | NULL              |
| 5030005 | 503      |   3 | NULL              |
| 6010001 | 601      |   3 | NULL              |
| 6010002 | 601      |   3 | NULL              |
| 6020001 | 602      |   3 | NULL              |
| 6020002 | 602      |   3 | NULL              |
| 6020003 | 602      |   3 | NULL              |
| 6020004 | 602      |   3 | NULL              |
| 6020005 | 602      |   3 | NULL              |
| 6030001 | 603      |   3 | NULL              |
| 6030002 | 603      |   3 | NULL              |
| 6030003 | 603      |   3 | NULL              |
| 6040001 | 604      |   3 | NULL              |
| 6040002 | 604      |   3 | NULL              |
| 6040003 | 604      |   3 | NULL              |
| 6050001 | 605      |   3 | NULL              |
| 6050002 | 605      |   3 | NULL              |
| 6050003 | 605      |   3 | NULL              |
| 6050004 | 605      |   3 | NULL              |
| 6060001 | 606      |   3 | NULL              |
| 6060002 | 606      |   3 | NULL              |
| 6060003 | 606      |   3 | NULL              |
| 6060004 | 606      |   3 | NULL              |
| 6060005 | 606      |   3 | NULL              |
| 6070001 | 607      |   3 | NULL              |
| 6070002 | 607      |   3 | NULL              |
| 6070003 | 607      |   3 | NULL              |
| 6070004 | 607      |   3 | NULL              |
| 7010001 | 701      |   3 | NULL              |
| 7010002 | 701      |   3 | NULL              |
| 8010001 | 801      |   3 | NULL              |
| 8010002 | 801      |   3 | NULL              |
| 8020001 | 802      |   3 | NULL              |
| 8020002 | 802      |   3 | NULL              |
| 8020003 | 802      |   3 | NULL              |
| 8030001 | 803      |   3 | NULL              |
| 8030002 | 803      |   3 | NULL              |
| 8030003 | 803      |   3 | NULL              |
| 8040001 | 804      |   3 | NULL              |
| 8040002 | 804      |   3 | NULL              |
| 8040003 | 804      |   3 | NULL              |
| 8050001 | 805      |   3 | NULL              |
| 8050002 | 805      |   3 | NULL              |
| 8050003 | 805      |   3 | NULL              |
| 8050004 | 805      |   3 | NULL              |
| 8050005 | 805      |   3 | NULL              |
| 8060001 | 806      |   3 | NULL              |
| 8060002 | 806      |   3 | NULL              |
+---------+----------+-----+-------------------+

Generating full path instead of linked hierarchy

If we are interested only in the full path N levels deep, we can omit ID and ParentID from the CTE. If we have a list of possible names in the supplementary table Names, it is easy to pick them from this table in CTE. The Names table should have enough rows for each level: 20 for level 1, 10 for level 2, 5 for level 3; 20+10+5 = 35 in total. It not necessary to have different sets of rows for each level, but it is easy to set it up properly, so I did it.

DECLARE @Names TABLE (Lvl int, Name nvarchar(4000), SeqNumber int);

-- First level: AAA, BBB, CCC, etc.
INSERT INTO @Names (Lvl, Name, SeqNumber)
SELECT 1, REPLICATE(CHAR(Number+64), 3) AS Name, Number AS SeqNumber
FROM Numbers
WHERE Number <= 20;

-- Second level: 001, 002, 003, etc.
INSERT INTO @Names (Lvl, Name, SeqNumber)
SELECT 2, REPLACE(STR(Number, 3), ' ', '0') AS Name, Number AS SeqNumber
FROM Numbers
WHERE Number <= 10;

-- Third level: I, II, III, IV, V
INSERT INTO @Names (Lvl, Name, SeqNumber) VALUES
(3, 'I',   1),
(3, 'II',  2),
(3, 'III', 3),
(3, 'IV',  4),
(3, 'V',   5);

SQL Fiddle Here is the final query. I split the FullPath into FilePath and FileName.

WITH
CTE
AS
(
    SELECT 
        TOP(CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 1)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 1)
            AS int))

        1 AS Lvl
        ,CAST(
            (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
            (
                1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = 2)
                  - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            )
            + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = 2)
            AS int) AS ChildRowCount
        ,N.Name AS FullPath
        ,N.Name AS [FilePath]
        ,CAST(N'' AS nvarchar(4000)) AS [FileName]
    FROM
        Numbers
        INNER JOIN @Names AS N ON 
            N.SeqNumber = Numbers.Number AND N.Lvl = 1
    ORDER BY Numbers.Number

    UNION ALL

    SELECT
        CTE.Lvl + 1 AS Lvl
        ,CA.ChildRowCount
        ,CTE.FullPath + '\' + CA.Name AS FullPath

        ,CASE WHEN CA.ChildRowCount IS NOT NULL 
            THEN CTE.FullPath + '\' + CA.Name
            ELSE CTE.FullPath END AS [FilePath]

        ,CASE WHEN CA.ChildRowCount IS NULL 
            THEN CA.Name
            ELSE N'' END AS [FileName]
    FROM
        CTE
        CROSS APPLY
        (
            SELECT
                Numbers.Number
                ,CAST(
                    (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) * 
                    (
                    1 + (SELECT I.LvlMax FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                      - (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    )
                    + (SELECT I.LvlMin FROM @Intervals AS I WHERE I.Lvl = CTE.Lvl + 2)
                    AS int) AS ChildRowCount
                ,N.Name
            FROM
                Numbers
                INNER JOIN @Names AS N ON 
                    N.SeqNumber = Numbers.Number AND N.Lvl = CTE.Lvl + 1
            WHERE Numbers.Number <= CTE.ChildRowCount
        ) AS CA
    WHERE
        CTE.ChildRowCount IS NOT NULL
)
SELECT
    CTE.FullPath
    ,CTE.[FilePath]
    ,CTE.[FileName]
FROM CTE
WHERE CTE.ChildRowCount IS NULL
ORDER BY FullPath;

Result

+-------------+----------+----------+
|  FullPath   | FilePath | FileName |
+-------------+----------+----------+
| AAA\001\I   | AAA\001  | I        |
| AAA\001\II  | AAA\001  | II       |
| AAA\002\I   | AAA\002  | I        |
| AAA\002\II  | AAA\002  | II       |
| AAA\002\III | AAA\002  | III      |
| AAA\002\IV  | AAA\002  | IV       |
| AAA\002\V   | AAA\002  | V        |
| AAA\003\I   | AAA\003  | I        |
| AAA\003\II  | AAA\003  | II       |
| AAA\003\III | AAA\003  | III      |
| AAA\004\I   | AAA\004  | I        |
| AAA\004\II  | AAA\004  | II       |
| AAA\004\III | AAA\004  | III      |
| AAA\004\IV  | AAA\004  | IV       |
| BBB\001\I   | BBB\001  | I        |
| BBB\001\II  | BBB\001  | II       |
| CCC\001\I   | CCC\001  | I        |
| CCC\001\II  | CCC\001  | II       |
| CCC\001\III | CCC\001  | III      |
| CCC\001\IV  | CCC\001  | IV       |
| CCC\001\V   | CCC\001  | V        |
| CCC\002\I   | CCC\002  | I        |
| CCC\003\I   | CCC\003  | I        |
| CCC\003\II  | CCC\003  | II       |
| CCC\004\I   | CCC\004  | I        |
| CCC\004\II  | CCC\004  | II       |
| CCC\005\I   | CCC\005  | I        |
| CCC\005\II  | CCC\005  | II       |
| CCC\005\III | CCC\005  | III      |
| CCC\006\I   | CCC\006  | I        |
| CCC\006\II  | CCC\006  | II       |
| CCC\006\III | CCC\006  | III      |
| CCC\006\IV  | CCC\006  | IV       |
| CCC\007\I   | CCC\007  | I        |
| CCC\007\II  | CCC\007  | II       |
| CCC\007\III | CCC\007  | III      |
| CCC\007\IV  | CCC\007  | IV       |
| CCC\008\I   | CCC\008  | I        |
| CCC\008\II  | CCC\008  | II       |
| CCC\008\III | CCC\008  | III      |
| CCC\009\I   | CCC\009  | I        |
| CCC\009\II  | CCC\009  | II       |
| CCC\009\III | CCC\009  | III      |
| CCC\009\IV  | CCC\009  | IV       |
| CCC\010\I   | CCC\010  | I        |
| CCC\010\II  | CCC\010  | II       |
| CCC\010\III | CCC\010  | III      |
| DDD\001\I   | DDD\001  | I        |
| DDD\001\II  | DDD\001  | II       |
| DDD\001\III | DDD\001  | III      |
| DDD\001\IV  | DDD\001  | IV       |
| DDD\002\I   | DDD\002  | I        |
| DDD\003\I   | DDD\003  | I        |
| DDD\003\II  | DDD\003  | II       |
| DDD\003\III | DDD\003  | III      |
| DDD\003\IV  | DDD\003  | IV       |
| DDD\004\I   | DDD\004  | I        |
| DDD\004\II  | DDD\004  | II       |
| DDD\004\III | DDD\004  | III      |
| DDD\005\I   | DDD\005  | I        |
| DDD\006\I   | DDD\006  | I        |
| DDD\006\II  | DDD\006  | II       |
| DDD\006\III | DDD\006  | III      |
| DDD\007\I   | DDD\007  | I        |
| DDD\007\II  | DDD\007  | II       |
| DDD\008\I   | DDD\008  | I        |
| DDD\008\II  | DDD\008  | II       |
| DDD\008\III | DDD\008  | III      |
| DDD\009\I   | DDD\009  | I        |
| DDD\009\II  | DDD\009  | II       |
| DDD\010\I   | DDD\010  | I        |
| DDD\010\II  | DDD\010  | II       |
| DDD\010\III | DDD\010  | III      |
| DDD\010\IV  | DDD\010  | IV       |
| DDD\010\V   | DDD\010  | V        |
| EEE\001\I   | EEE\001  | I        |
| EEE\001\II  | EEE\001  | II       |
| FFF\001\I   | FFF\001  | I        |
| FFF\002\I   | FFF\002  | I        |
| FFF\002\II  | FFF\002  | II       |
| FFF\003\I   | FFF\003  | I        |
| FFF\003\II  | FFF\003  | II       |
| FFF\003\III | FFF\003  | III      |
| FFF\003\IV  | FFF\003  | IV       |
| FFF\003\V   | FFF\003  | V        |
| FFF\004\I   | FFF\004  | I        |
| FFF\004\II  | FFF\004  | II       |
| FFF\004\III | FFF\004  | III      |
| FFF\004\IV  | FFF\004  | IV       |
| FFF\005\I   | FFF\005  | I        |
| FFF\006\I   | FFF\006  | I        |
| FFF\007\I   | FFF\007  | I        |
| FFF\007\II  | FFF\007  | II       |
| FFF\007\III | FFF\007  | III      |
| GGG\001\I   | GGG\001  | I        |
| GGG\001\II  | GGG\001  | II       |
| GGG\001\III | GGG\001  | III      |
| GGG\002\I   | GGG\002  | I        |
| GGG\003\I   | GGG\003  | I        |
| GGG\003\II  | GGG\003  | II       |
| GGG\003\III | GGG\003  | III      |
| GGG\004\I   | GGG\004  | I        |
| GGG\004\II  | GGG\004  | II       |
| HHH\001\I   | HHH\001  | I        |
| HHH\001\II  | HHH\001  | II       |
| HHH\001\III | HHH\001  | III      |
| HHH\002\I   | HHH\002  | I        |
| HHH\002\II  | HHH\002  | II       |
| HHH\002\III | HHH\002  | III      |
| HHH\002\IV  | HHH\002  | IV       |
| HHH\002\V   | HHH\002  | V        |
| HHH\003\I   | HHH\003  | I        |
| HHH\003\II  | HHH\003  | II       |
| HHH\003\III | HHH\003  | III      |
| HHH\003\IV  | HHH\003  | IV       |
| HHH\003\V   | HHH\003  | V        |
| HHH\004\I   | HHH\004  | I        |
| HHH\004\II  | HHH\004  | II       |
| HHH\004\III | HHH\004  | III      |
| HHH\004\IV  | HHH\004  | IV       |
| HHH\004\V   | HHH\004  | V        |
| HHH\005\I   | HHH\005  | I        |
| HHH\005\II  | HHH\005  | II       |
| HHH\005\III | HHH\005  | III      |
| HHH\005\IV  | HHH\005  | IV       |
| HHH\005\V   | HHH\005  | V        |
| HHH\006\I   | HHH\006  | I        |
| HHH\007\I   | HHH\007  | I        |
| HHH\007\II  | HHH\007  | II       |
| HHH\007\III | HHH\007  | III      |
| HHH\008\I   | HHH\008  | I        |
| HHH\008\II  | HHH\008  | II       |
| HHH\008\III | HHH\008  | III      |
| HHH\008\IV  | HHH\008  | IV       |
| HHH\008\V   | HHH\008  | V        |
+-------------+----------+----------+

Solomon Rutzky · Answer 3 · 2015-10-10T12:30:04+08:00

所以这就是我想出的。为了创建目录结构，我一直在为目录和文件寻找可用的“名称”。因为我无法TOP(n)在CROSS APPLYs 中工作（我想我试图通过使用来自父级的值作为nin来关联查询，TOP(n)但后来它不是随机的），我决定创建一种“数字”该表将允许一个INNER JOINorWHERE条件简单地通过随机化一个数字并将其指定为来生成一组n元素WHERE table.Level = random_number。诀窍是Level1只有1行，Level2只有2行，Level3只有3行，依此类推。因此， usingWHERE LevelID = 3将得到 3 行，每行都有一个值，我可以将其用作目录名称。

设置

这部分最初是内联指定的，作为 CTE 的一部分。但是为了可读性（这样你不需要滚动大量INSERT语句来获得真正查询的几行），我把它分解成一个本地临时表。

IF (OBJECT_ID(N'tempdb..#Elements') IS NULL)
BEGIN
  PRINT 'Creating #Elements table...';
  CREATE TABLE #Elements (
     ElementLevel TINYINT NOT NULL,
     LevelName NVARCHAR(50) NOT NULL
                         );

  PRINT 'Populating #Elements table...';
  INSERT INTO #Elements (ElementLevel, LevelName)
    SELECT tmp.[Level], tmp.[Name]
    FROM (
                  SELECT 1,  N'Ella'
       UNION ALL  SELECT 2,  N'Itchy'
       UNION ALL  SELECT 2,  N'Scratchy'
       UNION ALL  SELECT 3,  N'Moe'
       UNION ALL  SELECT 3,  N'Larry'
       UNION ALL  SELECT 3,  N'Curly'
       UNION ALL  SELECT 4,  N'Ian'
       UNION ALL  SELECT 4,  N'Stephen'
       UNION ALL  SELECT 4,  N'Peter'
       UNION ALL  SELECT 4,  N'Bernard'
       UNION ALL  SELECT 5,  N'Michigan'
       UNION ALL  SELECT 5,  N'Erie'
       UNION ALL  SELECT 5,  N'Huron'
       UNION ALL  SELECT 5,  N'Ontario'
       UNION ALL  SELECT 5,  N'Superior'
       UNION ALL  SELECT 6,  N'White'
       UNION ALL  SELECT 6,  N'Orange'
       UNION ALL  SELECT 6,  N'Blonde'
       UNION ALL  SELECT 6,  N'Pink'
       UNION ALL  SELECT 6,  N'Blue'
       UNION ALL  SELECT 6,  N'Brown'
       UNION ALL  SELECT 7,  N'Asia'
       UNION ALL  SELECT 7,  N'Africa'
       UNION ALL  SELECT 7,  N'North America'
       UNION ALL  SELECT 7,  N'South America'
       UNION ALL  SELECT 7,  N'Antarctica'
       UNION ALL  SELECT 7,  N'Europe'
       UNION ALL  SELECT 7,  N'Australia'
       UNION ALL  SELECT 8,  N'AA'
       UNION ALL  SELECT 8,  N'BB'
       UNION ALL  SELECT 8,  N'CC'
       UNION ALL  SELECT 8,  N'DD'
       UNION ALL  SELECT 8,  N'EE'
       UNION ALL  SELECT 8,  N'FF'
       UNION ALL  SELECT 8,  N'GG'
       UNION ALL  SELECT 8,  N'HH'
       UNION ALL  SELECT 9,  N'I'
       UNION ALL  SELECT 9,  N'II'
       UNION ALL  SELECT 9,  N'III'
       UNION ALL  SELECT 9,  N'IV'
       UNION ALL  SELECT 9,  N'V'
       UNION ALL  SELECT 9,  N'VI'
       UNION ALL  SELECT 9,  N'VII'
       UNION ALL  SELECT 9,  N'VIII'
       UNION ALL  SELECT 9,  N'IX'
       UNION ALL  SELECT 10, N'Million'
       UNION ALL  SELECT 10, N'Billion'
       UNION ALL  SELECT 10, N'Trillion'
       UNION ALL  SELECT 10, N'Quadrillion'
       UNION ALL  SELECT 10, N'Quintillion'
       UNION ALL  SELECT 10, N'Sestillion'
       UNION ALL  SELECT 10, N'Sextillion'
       UNION ALL  SELECT 10, N'Octillion'
       UNION ALL  SELECT 10, N'Nonillion'
       UNION ALL  SELECT 10, N'Decillion'
     ) tmp([Level], [Name]);
END;

主要查询

对于 1 级，我只是从中获取[name]值，sys.objects因为那里总是有很多行。但是，如果我需要对名称进行更多控制，我可以扩展#Elements表格以包含其他级别。

;WITH topdir(Level1, Randy) AS
(
    SELECT TOP ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 20) + 5 ) so.[name],
                ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 10) + 1 )
    FROM sys.objects so
    ORDER BY CRYPT_GEN_RANDOM(8) ASC
)
SELECT  td.Level1, tmp1.Level2, tmp2.Level3
FROM    topdir td
CROSS APPLY (SELECT help.LevelName, (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 5) + 1
            FROM #Elements help
            WHERE help.ElementLevel = td.Randy
            ) tmp1 (Level2, Bandy)
CROSS APPLY (SELECT help.LevelName
            FROM #Elements help
            WHERE help.ElementLevel = tmp1.Bandy
            ) tmp2 (Level3);

适合生成每个文件的路径、名称和内容的查询

为了生成文件和文件内容的完整路径，我将 CTE 的主 SELECT 设置为另一个 CTE，并添加了一个新的主 SELECT，它提供了只需要进入文件的正确输出。

DECLARE @Template NVARCHAR(4000);
SET @Template = N'<?xml version="1.0" encoding="ISO-8859-1"?>
<ns0:P4131 xmlns:ns0="http://switching/xi">
<R000000>
    <R00000010>R000000</R00000010>
    <R00000020>I</R00000020>
    <R00000030>{{Tag30}}</R00000030>
    <R00000040>{{Tag40}}</R00000040>
    <R00000050>{{Tag50}}</R00000050>
    <R00000060>2</R00000060>
</R000000>
</ns0:P4131>
';


;WITH topdir(Level1, Thing1) AS
(
    SELECT TOP ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 20) + 5 ) so.[name],
                ( (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 10) + 1 )
    FROM sys.objects so
    ORDER BY CRYPT_GEN_RANDOM(8) ASC
), main AS
(
   SELECT  td.Level1, tmp1.Level2, tmp2.Level3,
           td.Level1 + N'\' + tmp1.Level2 AS [FullPath],
           RIGHT('000' + CONVERT(VARCHAR(10),
                          (CONVERT(INT, CRYPT_GEN_RANDOM(2)) % 9999) + 1), 4) AS [R30],
           RIGHT('000' + CONVERT(VARCHAR(10),
                          (CONVERT(INT, CRYPT_GEN_RANDOM(2)) % 500) + 100), 4) AS [R50],
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [RowNum]
   FROM    topdir td
   CROSS APPLY (SELECT help.LevelName, (CONVERT(INT, CRYPT_GEN_RANDOM(1)) % 5) + 1
                FROM #Elements help
                WHERE help.ElementLevel = td.Thing1
               ) tmp1 (Level2, Thing2)
   CROSS APPLY (SELECT help.LevelName
                FROM #Elements help
                WHERE help.ElementLevel = tmp1.Thing2
               ) tmp2 (Level3)
)
SELECT  mn.FullPath,
        mn.Level3 + N'.xml' AS [FileName],
        REPLACE(
            REPLACE(
                REPLACE(
                    @Template,
                    N'{{Tag30}}',
                    mn.R30),
                N'{{Tag40}}',
                mn.RowNum),
            N'{{Tag50}}',
            mn.R50) AS [Contents]
FROM    main mn;

额外学分

While not part of the requirements stated in the question, the goal (which was mentioned) was to create files to test recursive File System functions with. So how do we take this result set of path names, file names, and file contents and do something with it? We just need two SQLCLR functions: one to create the folders and one to create the files.

In order to make this data functional, I modified the main SELECT of the CTE shown directly above as follows:

SELECT  SQL#.File_CreateDirectory(
            N'C:\Stuff\TestXmlFiles\' + mn.FullPath) AS [CreateTheDirectory],
        SQL#.File_WriteFile(
            N'C:\Stuff\TestXmlFiles\' + mn.FullPath + N'\' + mn.Level3 + N'.xml',
            REPLACE(
                REPLACE(
                    REPLACE(
                        @Template,
                        N'{{Tag30}}',
                        mn.R30),
                    N'{{Tag40}}',
                    mn.RowNum),
                N'{{Tag50}}',
                mn.R50), -- @FileData
            0, -- @AppendData
            '' -- @FileEncoding
                            ) AS [WriteTheFile]
FROM    main mn;

创建多个级别的层次结构，其中每个节点都有随机数量的子节点

Generating full path instead of linked hierarchy

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

创建多个级别的层次结构，其中每个节点都有随机数量的子节点

3 个回答

Generating full path instead of linked hierarchy

相关问题