Início / dba / Perguntas / 262498
Accepted
dharmatech
Asked: 2020-03-23 22:11:18 +0800 CST

Convertendo propriedades em colunas

  • 772

Tenho uma lista de tipos:

SELECT * FROM type;

id          name
----------- --------------------------------------------------
1           person
2           other god
3           location
4           role
5           gender

E uma lista de objetos, cada um com um tipo:

SELECT * FROM object;

id          name                                               type_id
----------- -------------------------------------------------- -----------
1           Adam                                               1
2           Eve                                                1
3           Cain                                               1
4           Abel                                               1
5           Jeroboam                                           1
6           Zeredah                                            3

E uma view para mostrar os nomes dos tipos:

SELECT * FROM object_view;

id          name                                               type_name
----------- -------------------------------------------------- --------------------------------------------------
1           Adam                                               person
2           Eve                                                person
3           Cain                                               person
4           Abel                                               person
5           Jeroboam                                           person
6           Zeredah                                            location

Uma lista de tipos de relacionamentos:

SELECT * FROM relationship;

id          name
----------- --------------------------------------------------
1           has father
2           has mother
3           from

E uma lista de relacionamentos entre objetos:

SELECT * FROM object_relationship;

object_a_id relationship_id object_b_id
----------- --------------- -----------
4           1               1
3           2               2
5           3               6

bem como uma visão dessas relações:

SELECT * FROM object_relationship_view;

object_a                                           relationship                                       object_b
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Abel                                               has father                                         Adam
Cain                                               has mother                                         Eve
Jeroboam                                           from                                               Zeredah

Eu gostaria de listar cada objeto com uma coluna para father, mothere from. Se um objeto não tiver uma dessas propriedades, a coluna deverá exibir NULL. Então o resultado deve ficar assim:

insira a descrição da imagem aqui

Aqui está uma abordagem que parece funcionar:

SELECT      id,

            object.name,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')
            ) AS father,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')
            ) AS mother,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'from')
            ) AS [from]

FROM        object;

Minha pergunta é a seguinte: isso pode ser feito via JOIN?

Esta abordagem é próxima:

SELECT      object.name,
            (SELECT name FROM object WHERE id = REL_FATHER.object_b_id) AS father,
            (SELECT name FROM object WHERE id = REL_MOTHER.object_b_id) AS mother,
            (SELECT name FROM object WHERE id = REL_FROM.object_b_id)   AS [from]

FROM        object
            LEFT JOIN   object_relationship AS REL_FATHER   ON  object.id = REL_FATHER.object_a_id
            LEFT JOIN   object_relationship AS REL_MOTHER   ON  object.id = REL_MOTHER.object_a_id
            LEFT JOIN   object_relationship AS REL_FROM     ON  object.id = REL_FROM.object_a_id

WHERE       REL_FATHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')    AND
            REL_MOTHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')    AND
            REL_FROM.relationship_id   = (SELECT id FROM relationship WHERE name = 'from');

O problema com essa abordagem é que ela apenas lista objetos que possuem valores para father, mothere from. Se algum deles for `NULL, eles não serão listados.

Assim, por exemplo, se você tiver dados de relacionamento adicionados pelo seguinte:

EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Abel', 'has mother', 'Eve';
EXEC insert_object_relationship 'Abel', 'from',       'Eden';

EXEC insert_object_relationship 'Cain', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Cain', 'from',       'Eden';

EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';

A consulta acima retorna o seguinte:

insira a descrição da imagem aqui

(Observe que Zeredahnão está listado, porque essa entrada não tem relacionamentos para fathere mother.

Existe uma abordagem que é melhor do que qualquer uma das mostradas acima?

Tenho certeza que a técnica descrita acima não é nova; quaisquer ponteiros para referências que discutem isso são bem-vindos. (Ou seja, existe um nome para isso em textos de teoria de banco de dados?)

Todo o código necessário para gerar essas tabelas e dados está incluído abaixo.

Se você acha que essa pergunta é mais adequada para o stackoverflow, me avise e eu pergunto por lá.

Obrigado por quaisquer sugestões!


DROP TABLE IF EXISTS object_relationship;
DROP TABLE IF EXISTS object;
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS type;

CREATE TABLE type
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL,
    type_id int             NOT NULL    CONSTRAINT FK_object_type FOREIGN KEY REFERENCES type(id)
);
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS relationship;

CREATE TABLE relationship
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object_relationship
(
    object_a_id     INT                 CONSTRAINT FK_object_relationship_object_object_a   FOREIGN KEY REFERENCES object(id),
    relationship_id INT                 CONSTRAINT FK_object_relationship_relationship      FOREIGN KEY REFERENCES relationship(id),
    object_b_id     INT                 CONSTRAINT FK_object_relationship_object_object_b   FOREIGN KEY REFERENCES object(id)
);
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_view;
GO

CREATE VIEW object_view

AS

SELECT  object.id, object.name AS name, type.name AS type_name
FROM    object INNER JOIN type ON object.type_id = type.id;

GO
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_relationship_view;
GO

CREATE VIEW object_relationship_view

AS

SELECT      A.name AS object_a, relationship.name AS relationship, B.name AS object_b
FROM        object AS A INNER JOIN object_relationship ON A.id = object_relationship.object_a_id
                        INNER JOIN relationship ON object_relationship.relationship_id = relationship.id
                        INNER JOIN object AS B ON B.id = object_relationship.object_b_id;
GO
--------------------------------------------------------------------------------
INSERT INTO type (name)
VALUES
('person'),
('other god'),
('location'),
('role'),
('gender');
DROP PROC IF EXISTS insert_object;      

GO

CREATE PROC insert_object
    @object     AS nvarchar(50),
    @type       AS nvarchar(50)

AS

INSERT INTO object (name, type_id)
VALUES
(@object, (SELECT id FROM type WHERE name = @type));

GO
--------------------------------------------------------------------------------
EXEC insert_object 'Adam',      'person';
EXEC insert_object 'Eve',       'person';
EXEC insert_object 'Cain',      'person';
EXEC insert_object 'Abel',      'person';
EXEC insert_object 'Jeroboam',  'person';
EXEC insert_object 'Zeredah',   'location';
EXEC insert_object 'Eden',  'location';
--------------------------------------------------------------------------------
INSERT INTO relationship (name)
VALUES
('has father'),
('has mother'),
('from');
--------------------------------------------------------------------------------
DROP PROC IF EXISTS insert_object_relationship;
GO

CREATE PROC insert_object_relationship
    @a  AS nvarchar(50),
    @relationship AS nvarchar(50),
    @b AS nvarchar(50)

AS

INSERT INTO object_relationship (object_a_id, relationship_id, object_b_id)
VALUES
((SELECT id FROM object WHERE name = @a), (SELECT id FROM relationship WHERE name = @relationship), (SELECT id FROM object WHERE name = @b));

GO
--------------------------------------------------------------------------------
EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';
sql-server t-sql

2 respostas

  1. Best Answer

    Tenho certeza que a técnica descrita acima não é nova...

    Certo, acho que o termo que você está procurando é "pivô". Você pode usar o T-SQLPIVOT para fazer isso:

    SELECT 
    pivot_table.aName,
    pivot_table.[has mother],
    pivot_table.[has father],
    pivot_table.[from]
FROM
(
    SELECT 
        oA.[name] AS aName, 
        oB.[name] AS bName, 
        r.[name] AS rName
    FROM dbo.[object] oA
    LEFT JOIN dbo.object_relationship ore
        ON ore.object_a_id = oA.id
    LEFT JOIN dbo.relationship r
        ON r.id = ore.relationship_id
    LEFT JOIN dbo.[object] oB
        ON ob.id = ore.object_b_id
) source_table
PIVOT
(
    MAX(bName)
    FOR rName IN ([has mother], [has father], [from])
) AS pivot_table
ORDER BY pivot_table.aName;

    E os resultados com os dados de exemplo fornecidos no script na parte inferior do seu post:

    captura de tela dos resultados da consulta no SSMS

    • 2

  2. Aqui está um outro abordado com base na resposta de Josh acima.

    SELECT *
FROM    object_relationship_view
PIVOT
(
    MAX(object_b) FOR relationship IN ([has mother], [has father], [from])
) AS PIVOT_TABLE
ORDER BY object_a;

    Em vez da subconsulta, ele usa object_relationship_view.

    Nesse caso, a saída é um pouco diferente, pois as linhas com NULLtodas as colunas não são geradas:

    insira a descrição da imagem aqui

    • 1

relate perguntas