Skip to content
Articoli

Creare una gerarchia di livelli multipli in cui ogni nodo ha un numero casuale di figli

Non dimenticare che nella scienza un errore ha quasi sempre soluzioni diverse, ma qui insegneremo il più ottimale e il migliore.

Soluzione:

(Nota di O.P: la soluzione preferita è il quarto/ultimo blocco di codice)

XML mi sembra la scelta più ovvia di struttura dati da usare in questo caso.

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

Il trucco per far sì che SQL Server utilizzi valori diversi per top() per ogni nodo è quello di rendere le sottoquery correlate. N1.N > 0 e N2.N > 0.

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

E una versione totalmente priva di 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;

Correlazione N1.N > 0 e N2.N > 0 è ancora importante.

Una versione che utilizza una tabella con 20 nomi da utilizzare al posto dei soli numeri interi.

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;

Interessante.

Il mio obiettivo era quello di generare un determinato numero di livelli con un numero casuale di righe figlio per ogni livello in una struttura gerarchica correttamente collegata. Una volta che questa struttura è pronta, è facile aggiungervi informazioni extra, come i nomi di file e cartelle.

Quindi, volevo generare una tabella classica per memorizzare un albero:

ID int NOT NULL
ParentID int NULL
Lvl int NOT NULL

Poiché abbiamo a che fare con la ricorsione, la CTE ricorsiva sembra una scelta naturale.

Avrò bisogno di una tabella di numeri. I numeri nella tabella devono partire da 1. Ci devono essere almeno 20 numeri nella tabella: 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;

I parametri per la generazione dei dati devono essere memorizzati in una tabella:

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

Si noti che la query è piuttosto flessibile e tutti i parametri sono separati in un unico posto. Se necessario, si possono aggiungere altri livelli, basta aggiungere una riga supplementare di parametri.

Per rendere possibile questa generazione dinamica, ho dovuto ricordare il numero casuale di righe per il livello successivo, quindi ho una colonna in più ChildRowCount.

Generazione unicoIDs è anch'esso un po' complicato. Ho codificato in modo rigido il limite di 100 righe figlio per 1 riga genitore per garantire che IDs non si ripetano. Questo è ciò che POWER(100, CTE.Lvl) si riferisce a questo. Di conseguenza, ci sono grandi lacune in IDs. Quel numero potrebbe essere un MAX(LvlMax)ma ho inserito la costante 100 nella query per semplicità. Il numero di livelli non è codificato in modo rigido, ma è determinato da @Intervals.

Questa formula

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

genera un numero casuale in virgola mobile nell'intervallo [0..1)che viene poi scalato all'intervallo richiesto.

La logica della query è semplice. È ricorsiva. Il primo passo genera un insieme di righe del primo livello. Il numero di righe è determinato da un numero casuale in TOP. Inoltre, per ogni riga c'è un numero casuale separato di righe figlie memorizzato in ChildRowCount.

La parte ricorsiva utilizza CROSS APPLY per generare un determinato numero di righe figlio per ogni riga genitore. Ho dovuto usare WHERE Numbers.Number <= CTE.ChildRowCount invece di TOP(CTE.ChildRowCount)perché TOP non è consentito nella parte ricorsiva di CTE. Non sapevo di questa limitazione di SQL Server.

WHERE CTE.ChildRowCount IS NOT NULL interrompe la ricorsione.

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;

Risultato (ci possono essere fino a 20 + 20*10 + 200*5 = 1220 righe se si è fortunati)

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

Generazione del percorso completo invece della gerarchia collegata

Se siamo interessati solo al percorso completo N livelli di profondità, possiamo omettere ID e ParentID dal CTE. Se abbiamo un elenco di nomi possibili nella tabella supplementare Namesè facile selezionarli da questa tabella in CTE. La tabella Names deve avere un numero sufficiente di righe per ogni livello: 20 per il livello 1, 10 per il livello 2, 5 per il livello 3; 20+10+5 = 35 in totale. Non è necessario avere diverse serie di righe per ogni livello, ma è facile impostarle correttamente, quindi l'ho fatto.

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 Ecco la query finale. Ho diviso i campi FullPath in FilePath e 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;

Risultato

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

Ecco cosa ho ottenuto. Con l'obiettivo di creare una struttura di directory, stavo cercando dei "nomi" utilizzabili per le directory e i file. Poiché non sono riuscito a ottenere il nome TOP(n) funziona nella cartella CROSS APPLY(credo di aver tentato di mettere in relazione le query usando un valore del genitore come il nome n nel campo TOP(n) ma non era casuale), ho deciso di creare un tipo di tabella "numeri" che permettesse di inserire un valore INNER JOIN o WHERE di produrre un insieme di n semplicemente randomizzando un numero e specificandolo come WHERE table.Level = random_number. Il trucco è che c'è solo una riga per il livello 1, 2 righe per il livello 2, 3 righe per il livello 3 e così via. Quindi, utilizzando WHERE LevelID = 3 si otterranno 3 righe e ogni riga ha un valore che si può usare come nome di directory.

IMPOSTAZIONE

Questa parte era originariamente specificata in linea, come parte del CTE. Ma per motivi di leggibilità (in modo da non dover scorrere molti file INSERT per arrivare alle poche righe della query vera e propria), l'ho suddivisa in una tabella temporanea 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;

QUERY PRINCIPALE

Per il livello 1 ho semplicemente preso [name] da sys.objects poiché ci sono sempre molte righe lì. Ma se avessi bisogno di un maggiore controllo sui nomi, potrei semplicemente espandere il file #Elements per contenere altri livelli.

;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 ADATTATA PER PRODURRE IL PERCORSO, IL NOME E IL CONTENUTO DI OGNI FILE

Per generare i percorsi completi dei file e il loro contenuto, ho fatto in modo che la SELECT principale della CTE fosse un'altra CTE e ho aggiunto una nuova SELECT principale che forniva gli output corretti che dovevano semplicemente andare nei file.

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;

CREDITO EXTRA

Anche se non fa parte dei requisiti indicati nella domanda, l'obiettivo (che è stato menzionato) era quello di creare dei file con cui testare le funzioni ricorsive del File System. Quindi, come possiamo prendere questo insieme di risultati di nomi di percorsi, nomi di file e contenuti di file e farne qualcosa? Abbiamo bisogno di due funzioni SQLCLR: una per creare le cartelle e una per creare i file.

Per rendere funzionali questi dati, ho modificato la funzione principale SELECT del CTE mostrato direttamente sopra, come segue:

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;

Tags : / / / /

Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Domande correlate:

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.