Skip to content

Créer une hiérarchie de plusieurs niveaux où chaque nœud a un nombre aléatoire d'enfants.

Gardez à l'esprit qu'en informatique une erreur a presque toujours plusieurs résolutions, mais nous enseignons la plus optimale et la meilleure.

Solution :

(Note de l'O.P : la solution préférée est le 4ème / dernier bloc de code)

XML me semble être le choix évident de la structure de données à utiliser ici.

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');

L'astuce pour faire en sorte que le serveur SQL utilise différentes valeurs de top() pour chaque nœud est de rendre les sous-requêtes corrélées. N1.N > 0 et N2.N > 0.

Flatte le 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);

Et une version totalement dépourvue de 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;

Corrélation N1.N > 0 et N2.N > 0 est toujours importante.

Une version utilisant un tableau avec 20 noms à utiliser au lieu de simples entiers.

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;

C'était intéressant.

Mon objectif était de générer un nombre donné de niveaux avec un nombre aléatoire de lignes enfants par chaque niveau dans une structure hiérarchique correctement liée. Une fois que cette structure est prête, il est facile d'y ajouter des infos supplémentaires comme les noms de fichiers et de dossiers.

Je voulais donc générer une table classique pour stocker une arborescence :

ID int NOT NULL
ParentID int NULL
Lvl int NOT NULL

Puisque nous avons affaire à la récursion, le CTE récursif semble un choix naturel.

J'aurai besoin d'un tableau de nombres. Les nombres dans la table doivent commencer à partir de 1. Il doit y avoir au moins 20 nombres dans la 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;

Les paramètres pour la génération de données doivent être stockés dans une 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);

Notez, que la requête est assez flexible et que tous les paramètres sont séparés en un seul endroit. Vous pouvez ajouter plus de niveaux si nécessaire, il suffit d'ajouter une ligne supplémentaire de paramètres.

Pour rendre cette génération dynamique possible, j'ai dû me souvenir du nombre aléatoire de lignes pour le niveau suivant, j'ai donc une colonne supplémentaire... ChildRowCount.

Génération de uniqueIDs est également quelque peu délicate. J'ai codé en dur la limite de 100 lignes enfants pour 1 ligne parent pour garantir que IDs ne se répète pas. Voici ce que cela donne POWER(100, CTE.Lvl) est en cause. En conséquence, il y a de grandes lacunes dans les IDs. Ce nombre pourrait être un MAX(LvlMax), mais j'ai mis la constante 100 dans la requête pour plus de simplicité. Le nombre de niveaux n'est pas codé en dur, mais est déterminé par @Intervals.

Cette formule

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

génère un nombre aléatoire à virgule flottante dans l'intervalle [0..1), qui est ensuite mis à l'échelle de l'intervalle requis.

La logique de la requête est simple. Elle est récursive. La première étape génère un ensemble de lignes du premier niveau. Le nombre de rangées est déterminé par un nombre aléatoire dans TOP. De plus, pour chaque rangée, il existe un nombre aléatoire distinct de rangées enfants stockées dans ChildRowCount.

La partie récursive utilise CROSS APPLY pour générer un nombre donné de lignes enfants pour chaque ligne parent. J'ai dû utiliser WHERE Numbers.Number <= CTE.ChildRowCount au lieu de TOP(CTE.ChildRowCount)car TOP n'est pas autorisé dans la partie récursive du CTE. Je ne connaissais pas cette limitation de SQL Server auparavant.

WHERE CTE.ChildRowCount IS NOT NULL arrête la récursion.

Fiddle SQL

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;

Résultat (il peut y avoir jusqu'à 20 + 20*10 + 200*5 = 1220 lignes si vous êtes chanceux)

+---------+----------+-----+-------------------+
|   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              |
+---------+----------+-----+-------------------+

Génération du chemin complet au lieu de la hiérarchie liée

Si nous sommes intéressés uniquement par le chemin complet N niveaux de profondeur, nous pouvons omettre ID et ParentID dans le CTE. Si nous avons une liste de noms possibles dans le tableau supplémentaire Namesil est facile de les choisir dans cette table en CTE. Le site Names doit avoir suffisamment de lignes pour chaque niveau : 20 pour le niveau 1, 10 pour le niveau 2, 5 pour le niveau 3; 20+10+5 = 35 au total. Il n'est pas nécessaire d'avoir différents ensembles de lignes pour chaque niveau, mais il est facile de le mettre en place correctement, donc je l'ai fait.

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);

Fiddle SQL Voici la requête finale. J'ai divisé le FullPath en FilePath et 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;

Résultat

+-------------+----------+----------+
|  FullPath   | FilePath | FileName |
+-------------+----------+----------+
| AAA01I   | AAA01  | I        |
| AAA01II  | AAA01  | II       |
| AAA02I   | AAA02  | I        |
| AAA02II  | AAA02  | II       |
| AAA02III | AAA02  | III      |
| AAA02IV  | AAA02  | IV       |
| AAA02V   | AAA02  | V        |
| AAA03I   | AAA03  | I        |
| AAA03II  | AAA03  | II       |
| AAA03III | AAA03  | III      |
| AAA04I   | AAA04  | I        |
| AAA04II  | AAA04  | II       |
| AAA04III | AAA04  | III      |
| AAA04IV  | AAA04  | IV       |
| BBB01I   | BBB01  | I        |
| BBB01II  | BBB01  | II       |
| CCC01I   | CCC01  | I        |
| CCC01II  | CCC01  | II       |
| CCC01III | CCC01  | III      |
| CCC01IV  | CCC01  | IV       |
| CCC01V   | CCC01  | V        |
| CCC02I   | CCC02  | I        |
| CCC03I   | CCC03  | I        |
| CCC03II  | CCC03  | II       |
| CCC04I   | CCC04  | I        |
| CCC04II  | CCC04  | II       |
| CCC05I   | CCC05  | I        |
| CCC05II  | CCC05  | II       |
| CCC05III | CCC05  | III      |
| CCC06I   | CCC06  | I        |
| CCC06II  | CCC06  | II       |
| CCC06III | CCC06  | III      |
| CCC06IV  | CCC06  | IV       |
| CCC07I   | CCC07  | I        |
| CCC07II  | CCC07  | II       |
| CCC07III | CCC07  | III      |
| CCC07IV  | CCC07  | IV       |
| CCC08I   | CCC08  | I        |
| CCC08II  | CCC08  | II       |
| CCC08III | CCC08  | III      |
| CCC09I   | CCC09  | I        |
| CCC09II  | CCC09  | II       |
| CCC09III | CCC09  | III      |
| CCC09IV  | CCC09  | IV       |
| CCC10I   | CCC10  | I        |
| CCC10II  | CCC10  | II       |
| CCC10III | CCC10  | III      |
| DDD01I   | DDD01  | I        |
| DDD01II  | DDD01  | II       |
| DDD01III | DDD01  | III      |
| DDD01IV  | DDD01  | IV       |
| DDD02I   | DDD02  | I        |
| DDD03I   | DDD03  | I        |
| DDD03II  | DDD03  | II       |
| DDD03III | DDD03  | III      |
| DDD03IV  | DDD03  | IV       |
| DDD04I   | DDD04  | I        |
| DDD04II  | DDD04  | II       |
| DDD04III | DDD04  | III      |
| DDD05I   | DDD05  | I        |
| DDD06I   | DDD06  | I        |
| DDD06II  | DDD06  | II       |
| DDD06III | DDD06  | III      |
| DDD07I   | DDD07  | I        |
| DDD07II  | DDD07  | II       |
| DDD08I   | DDD08  | I        |
| DDD08II  | DDD08  | II       |
| DDD08III | DDD08  | III      |
| DDD09I   | DDD09  | I        |
| DDD09II  | DDD09  | II       |
| DDD10I   | DDD10  | I        |
| DDD10II  | DDD10  | II       |
| DDD10III | DDD10  | III      |
| DDD10IV  | DDD10  | IV       |
| DDD10V   | DDD10  | V        |
| EEE01I   | EEE01  | I        |
| EEE01II  | EEE01  | II       |
| FFF01I   | FFF01  | I        |
| FFF02I   | FFF02  | I        |
| FFF02II  | FFF02  | II       |
| FFF03I   | FFF03  | I        |
| FFF03II  | FFF03  | II       |
| FFF03III | FFF03  | III      |
| FFF03IV  | FFF03  | IV       |
| FFF03V   | FFF03  | V        |
| FFF04I   | FFF04  | I        |
| FFF04II  | FFF04  | II       |
| FFF04III | FFF04  | III      |
| FFF04IV  | FFF04  | IV       |
| FFF05I   | FFF05  | I        |
| FFF06I   | FFF06  | I        |
| FFF07I   | FFF07  | I        |
| FFF07II  | FFF07  | II       |
| FFF07III | FFF07  | III      |
| GGG01I   | GGG01  | I        |
| GGG01II  | GGG01  | II       |
| GGG01III | GGG01  | III      |
| GGG02I   | GGG02  | I        |
| GGG03I   | GGG03  | I        |
| GGG03II  | GGG03  | II       |
| GGG03III | GGG03  | III      |
| GGG04I   | GGG04  | I        |
| GGG04II  | GGG04  | II       |
| HHH01I   | HHH01  | I        |
| HHH01II  | HHH01  | II       |
| HHH01III | HHH01  | III      |
| HHH02I   | HHH02  | I        |
| HHH02II  | HHH02  | II       |
| HHH02III | HHH02  | III      |
| HHH02IV  | HHH02  | IV       |
| HHH02V   | HHH02  | V        |
| HHH03I   | HHH03  | I        |
| HHH03II  | HHH03  | II       |
| HHH03III | HHH03  | III      |
| HHH03IV  | HHH03  | IV       |
| HHH03V   | HHH03  | V        |
| HHH04I   | HHH04  | I        |
| HHH04II  | HHH04  | II       |
| HHH04III | HHH04  | III      |
| HHH04IV  | HHH04  | IV       |
| HHH04V   | HHH04  | V        |
| HHH05I   | HHH05  | I        |
| HHH05II  | HHH05  | II       |
| HHH05III | HHH05  | III      |
| HHH05IV  | HHH05  | IV       |
| HHH05V   | HHH05  | V        |
| HHH06I   | HHH06  | I        |
| HHH07I   | HHH07  | I        |
| HHH07II  | HHH07  | II       |
| HHH07III | HHH07  | III      |
| HHH08I   | HHH08  | I        |
| HHH08II  | HHH08  | II       |
| HHH08III | HHH08  | III      |
| HHH08IV  | HHH08  | IV       |
| HHH08V   | HHH08  | V        |
+-------------+----------+----------+

Voici donc ce que j'ai trouvé. Dans le but de créer une structure de répertoires, je cherchais des "noms" utilisables pour les répertoires et les fichiers. Comme je n'ai pas réussi à obtenir le nom TOP(n) dans le répertoire CROSS APPLY(je pense que j'ai essayé de corréler les requêtes en utilisant une valeur du parent comme le n . n dans la requête TOP(n) mais alors ce n'était pas aléatoire), j'ai décidé de créer un type de table de "nombres" qui permettrait à un INNER JOIN ou WHERE de produire un ensemble de nsimplement en randomisant un nombre et en le spécifiant en tant que WHERE table.Level = random_number. L'astuce est qu'il n'y a qu'une seule ligne pour le niveau 1, 2 lignes pour le niveau 2, 3 lignes pour le niveau 3, et ainsi de suite. Ainsi, en utilisant WHERE LevelID = 3 me donnera 3 lignes, et chaque ligne a une valeur que je peux utiliser comme nom de répertoire.

SETUP

Cette partie était à l'origine spécifiée en ligne, dans le cadre de l'ETC. Mais pour des raisons de lisibilité (pour que vous n'ayez pas à faire défiler beaucoup de... INSERT pour arriver aux quelques lignes de la vraie requête), je l'ai décomposée dans une table temporaire locale.

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;

REQUÊTE PRINCIPALE

Pour le niveau 1, j'ai juste saisi [name] à partir de sys.objects puisqu'il y a toujours beaucoup de lignes à cet endroit. Mais, si j'avais besoin d'un plus grand contrôle sur les noms, je pourrais simplement étendre la zone #Elements pour contenir des niveaux supplémentaires.

;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);

QUERY ADAPTÉE POUR PRODUIRE LE CHEMIN, LE NOM et LE CONTENU DE CHAQUE FICHIER

Afin de générer les chemins complets pour les fichiers et le contenu des fichiers, j'ai fait du SELECT principal du CTE juste un autre CTE et j'ai ajouté un nouveau SELECT principal qui a donné les sorties appropriées qui doivent simplement aller dans les fichiers.

DECLARE @Template NVARCHAR(4000);
SET @Template = N'


    R000000
    I
    {{Tag30}}
    {{Tag40}}
    {{Tag50}}
    2


';

;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;

CREDIT SUPPLEMENTAIRE

Bien que cela ne fasse pas partie des exigences énoncées dans la question, l'objectif (qui a été mentionné) était de créer des fichiers pour tester les fonctions récursives du système de fichiers avec. Alors comment prendre cet ensemble de résultats de noms de chemins, de noms de fichiers et de contenus de fichiers et en faire quelque chose ? Nous avons juste besoin de deux fonctions SQLCLR : une pour créer les dossiers et une pour créer les fichiers.

Afin de rendre ces données fonctionnelles, j'ai modifié la fonction principale. SELECT de l'ETC présenté directement ci-dessus comme suit :

SELECT  SQL#.File_CreateDirectory(
            N'C:StuffTestXmlFiles' + mn.FullPath) AS [CreateTheDirectory],
        SQL#.File_WriteFile(
            N'C:StuffTestXmlFiles' + 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;

Section des critiques et des évaluations

Si vous avez trouvé cet article utile, il serait très utile que vous le partagiez avec le reste des développeurs et que vous nous aidiez à diffuser notre contenu.



Utilisez notre moteur de recherche

Ricerca
Generic filters

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.