respaldo - ruta de base de datos sql server 2014

SQL: busca rutas de carpetas faltantes en las jerarquías de división

Question

Tengo una tabla que contiene rutas de carpetas. Esta tabla contiene cuatro columnas: DirID , BaseDirID , DirLevel y DisplayPath . DirID : la identificación de la carpeta. BaseDirID : el ID de la primera carpeta en la jerarquía. De modo que todas las carpetas (rutas) de la misma jerarquía comparten el mismo valor en esta columna. DirLevel - La profundidad de la carpeta. DisplayPath : la ruta de la carpeta.

Necesito encontrar todos los "espacios" entre esas carpetas en la jerarquía.

Datos de muestra por ejemplo:

DirID BaseDirID DirLevel DisplayPath
1   1  1  ''A''
2   1  3  ''A/B/C''
3   1  5  ''A/B/C/D/E''
4   1  3  ''A/B/F''
5   1  5  ''A/B/F/G/H''
6   2  1  ''U''
7   2  3  ''U/V/W''
8   2  5  ''U/V/W/X/Y''
9   2  3  ''U/V/M''
10  2  5  ''U/V/M/L/O''

Entonces, necesitamos encontrar la siguiente información:

BaseDirID DisplayPath
1   ''A/B''
1   ''A/B/C/D''
1   ''A/B/F/G''
2   ''U/V''
2   ''U/V/W/X''
2   ''U/V/M/L''

Comentarios:

a. Esta tabla contiene más de 250,000 registros de carpetas, por lo que buscamos la manera más eficiente de hacerlo , de lo contrario, el script estará bloqueado durante mucho tiempo, tiempo que no tenemos.

segundo. No tengo una lista de todas las carpetas. Lo que tengo son las carpetas "raíz" y las carpetas "hojas" que necesito para encontrar las "brechas" entre ellos en la jerarquía.

do. La tabla puede contener más de una jerarquía y necesitamos encontrar las "brechas" en todas las jerarquías.

re. Cada una de las jerarquías puede dividirse, como se puede ver en los datos de muestra, la primera jerarquía se divide en dos rutas de carpetas de la carpeta ''A / B'': ''A / B / C'' y ''A / B / F''. Y la segunda jerarquía se divide en dos rutas de carpetas de la carpeta ''U / V'': ''U / V / W'' y ''U / V / M''. Y debemos encontrar todas las "brechas" incluso en tales casos cuando las jerarquías se dividen.

mi. La versión del servidor sql es: SQL 2012 SP3.

Esta pregunta es una pregunta de continuación para la pregunta que se hizo en el siguiente enlace: SQL: encuentra las carpetas faltantes de jerarquía (rutas) en una tabla. Nuestra pregunta también incluye el cuarto comentario que aparece en negrita.

Vi que hay un nuevo tipo de columna que llama "hierarchyid" (desde el servidor sql 2008), que pensé que podría ayudarnos - https://docs.microsoft.com/en-us/sql/t-sql/ tipos de datos / hierarchyid-data-type-method-reference ¿Qué opinas?

Gracias por adelantado.

Answer 1

Usando esta ruta adicional (11,2,''U/V/Z/L/O/Q/R/S/T'') para mostrar múltiples carpetas faltantes en una ruta:

with cte as (
select BaseDirID, DisplayPath = left(DisplayPath,len(DisplayPath)-charindex(''/',reverse(DisplayPath)))
from t
where DirLevel > 1
  and not exists (
  select 1 
  from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath = left(t.DisplayPath,len(t.DisplayPath)-charindex(''/',reverse(t.DisplayPath)))
    )
union all 
select BaseDirID, DisplayPath = left(DisplayPath,len(DisplayPath)-charindex(''/',reverse(DisplayPath)))
from cte t
where not exists (
  select 1 
  from t i
  where t.BaseDirId = i.BaseDirId
    and i.DisplayPath = left(t.DisplayPath,len(t.DisplayPath)-charindex(''/',reverse(t.DisplayPath)))                                                   
    )
)
select distinct * 
from cte

demostración de rextester: http://rextester.com/CEVGZ96613

devoluciones:

+-----------+-----------------+
| BaseDirID |   DisplayPath   |
+-----------+-----------------+
|         1 | A/B             |
|         1 | A/B/C/D         |
|         1 | A/B/F/G         |
|         2 | U/V             |
|         2 | U/V/M/L         |
|         2 | U/V/W/X         |
|         2 | U/V/Z           |
|         2 | U/V/Z/L         |
|         2 | U/V/Z/L/O       |
|         2 | U/V/Z/L/O/Q     |
|         2 | U/V/Z/L/O/Q/R   |
|         2 | U/V/Z/L/O/Q/R/S |
+-----------+-----------------+