SQL Server - 使用聚集索引时如何存储数据页

Question

dharmatech

Asked: 2020-03-23 22:11:18 +0800 CST2020-03-23 22:11:18 +0800 CST 2020-03-23 22:11:18 +0800 CST

将属性转换为列

772

我有一个类型列表：

SELECT * FROM type;

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

以及一个对象列表，每个对象都有一个类型：

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

以及显示类型名称的视图：

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

关系类型列表：

SELECT * FROM relationship;

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

以及对象之间的关系列表：

SELECT * FROM object_relationship;

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

以及对这些关系的看法：

SELECT * FROM object_relationship_view;

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

我想用、和列列出father每个mother对象from。如果对象不具有这些属性之一，则该列应显示NULL. 所以结果应该是这样的：

这是一种似乎可行的方法：

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;

我的问题是：这可以通过 JOIN 来完成吗？

这种方法很接近：

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

这种方法的问题在于它只列出了具有和值father的对象。如果其中任何一个为 `NULL，则不会列出它们。motherfrom

因此，例如，如果您有以下添加的关系数据：

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

上面的查询返回以下内容：

（请注意未列出，因为该条目与和Zeredah没有关系。fathermother

有没有比上面显示的方法更好的方法？

我确信上面描述的技术不是新的；欢迎任何指向讨论此问题的参考文献的指针。（即在数据库理论文本中有这个名称吗？）

生成这些表和数据所需的所有代码都包含在下面。

如果你觉得这个问题更适合stackoverflow，请告诉我，我会在那里问。

感谢您的任何建议！


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

2 个回答

Voted

Josh Darnell · Answer 1 · 2020-03-24T07:05:04+08:00

Best Answer

Josh Darnell

2020-03-24T07:05:04+08:002020-03-24T07:05:04+08:00

我确信上面描述的技术不是新的......

对，我认为您正在寻找的术语是“枢轴”。您可以使用 T-SQLPIVOT运算符来执行此操作：

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;

以及帖子底部脚本中给出的示例数据的结果：

2

dharmatech · Answer 2 · 2020-03-24T13:48:11+08:00

dharmatech

2020-03-24T13:48:11+08:002020-03-24T13:48:11+08:00

这是基于 Josh 上述回答的另一种方法。

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

它使用object_relationship_view.

在这种情况下，输出略有不同，因为NULL所有列中的行都没有输出：

1

将属性转换为列

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

将属性转换为列

2 个回答

相关问题