Início / dba / Perguntas / 267459
Accepted
David Allen
Asked: 2020-05-20 07:19:45 +0800 CST

Cache de relacionamento de localização pai-filho recursivo como visualização

  • 772

Aqui está minha estrutura de dados:

CREATE TABLE Locations(
    LocID int IDENTITY(1,1) NOT NULL,
    LocationTypeID int NOT NULL,
    ParentID int NULL,
    LocNum varchar(50) NULL,
    LocName varchar(250) NULL,
)

CREATE TABLE LocationTypes(
    LocationTypeID int IDENTITY(1,1) NOT NULL,
    TypeName varchar(50) NOT NULL
)

A tabela LocationTypes contém os seguintes valores:

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+

A Locationstabela contém vários locais, alguns em que o ParentIDé nulo (Campus) e outros que têm um ParentID. Existem 5 níveis de hierarquia para minha estrutura de dados, indicados na LocationTypestabela. Campus (1) sendo o menos específico, e Room(5) sendo o mais específico. No meu banco de dados eu tenho outras tabelas que referenciam o LocID, na maioria das vezes essas tabelas estão referenciando Locations com um LocationTypeIDof 5, mas nem sempre.

Eu preciso obter os nomes de cada local na hierarquia para exibição ao usuário final.

Em última análise, o que eu quero obter é uma visão (ou tabela em cache) com a seguinte estrutura:

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+

Percebo que algumas dessas colunas acabarão sendo nulas, para Locations com LocationTypeIDalgo diferente de 5, e isso está perfeitamente bem.

Eu sei que provavelmente preciso de uma expressão de tabela comum recursiva, só não sei por onde começar.

Basicamente, a lógica do pseudo-código para isso é:

  • Foreach LocationnoLocationsTable
  • Obtenha o Parent Locationde cada Location, continue recursivamente até que não haja mais pais
  • Armazene esses dados em cache em uma exibição, para que possam ser facilmente LEFT JOINeditados em qualquer outra tabela que faça referência a umLocID

Espero que tudo faça sentido. Obrigado quem puder me ajudar.

sql-server view

1 respostas

  1. Best Answer

    Acredito que você possa alcançar o resultado desejado com esta consulta:

    ;WITH CTE_Locations (LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName)
AS
(
    SELECT LocID, 
        LocName AS CampusLocName, 
        CONVERT(varchar(250), NULL) AS BuildingLocName, 
        CONVERT(varchar(250), NULL) AS AreaLocName, 
        CONVERT(varchar(250), NULL) AS FloorLocName, 
        CONVERT(varchar(250), NULL) AS RoomLocName
    FROM Locations
    WHERE ParentID IS NULL

    UNION ALL

    SELECT L.LocID, 
        CampusLocName, 
        IIF(L.LocationTypeID = 2, LocName, BuildingLocName) AS BuildingLocName, 
        IIF(L.LocationTypeID = 3, LocName, AreaLocName) AS AreaLocName, 
        IIF(L.LocationTypeID = 4, LocName, FloorLocName) AS FloorLocName, 
        IIF(L.LocationTypeID = 5, LocName, RoomLocName) AS RoomLocName
    FROM Locations L
        INNER JOIN CTE_Locations CTE ON L.ParentID = CTE.LocID 
)
SELECT LocID, CampusLocName, BuildingLocName, AreaLocName, FloorLocName, RoomLocName
FROM CTE_Locations;

    De acordo com o documento COM common_table_expression :

    Essa cláusula também pode ser usada em uma instrução CREATE VIEW como parte de sua instrução SELECT de definição.

    • 1

relate perguntas