Uniones recursivas.
Tengo dos tablas definidas a continuación (tenga en cuenta que la tabla Regions es recursiva y esa recursión puede tener muchos niveles potencialmente).
Regiones
Id | ParentId | Name |
---|---|---|
1 | null | EU |
2 | 1 | Alemania |
3 | 1 | Francia |
Ciudades
Id | Name | RegionId |
---|---|---|
1 | Berlín | 2 |
2 | Hamburgo | 2 |
3 | París | 3 |
4 | Niza | 3 |
Quiero ver cuántas ciudades hay en una región en particular. Resultado deseado a continuación:
Región | Conteo de ciudades |
---|---|
EU | 4 |
Alemania | 2 |
Francia | 2 |
Esta consulta me da el conteo de ciudades en cada región hija, pero ¿cómo puedo unir la tabla recursiva para obtener también la región principal (en este caso EU
)?
select R.Name, count(C.Id)
from Regions R
join Cities C on C.RegionId = R.Id
group by R.Name
having count(C.Id) > 1
He intentado simplificar un problema real que estoy enfrentando, esta es obviamente la simplificación.
davy.ai
Para incluir la región padre en la salida, puede usar una consulta recursiva para recorrer la jerarquía de la tabla Regiones. Aquí hay una consulta de ejemplo que debería darle la salida deseada:
Esta consulta utiliza una expresión de tabla común (CTE) para definir una consulta recursiva que comienza con las regiones de nivel superior (aquellas con un
parentid
denull
) y recorre la jerarquía usando unjoin
con la CTE de lajerarquía recursiva de la región
. Para cada región en la jerarquía, incluimos elid
,parentid
, ynombre
, así como una columnaregion_raíz
que especifica la región de nivel superior que contiene la región actual.Una vez que hemos definido la CTE de
jerarquía recursiva de la región
, podemos unirla con la tablaciudades
para contar el número de ciudades en cada región. Finalmente, agrupamos los resultados por la columnaregion_raíz
para obtener la salida deseada.