SQL Server 中的链接记录视图

Question

我有以下 E/R：

type ----<  record ----< record_link

记录表具有 FK from 类型，record_link表存储两个记录之间的关系

请参阅下表详细信息（我省略了PK）：

type
-------------------
name        typeId
-------------------
type1       1 
type2       2
type3       3
type4       4
type5       5
type6       6
type7       7
type8       8
type9       9

record
---------------------------------
recordId    name        typeId
---------------------------------
1           name1        1 
2           name2        2 
3           name3        3
4           name4        4
5           name5        5
6           name6        6
7           name7        7
8           name8        8
9           name9        9

record_link
------------------------
recordId    recordLinkedId
------------------------
9           8 
8           7
7           8
6           8
6           7
3           2

我正在尝试在 TSQL 中构建一个视图，该视图列出了相关记录及其类型

my_view
--------------------------------------------------------------------------
recordId    name    linkedRecordId  recordTypeName  linkedRecordTypeName
--------------------------------------------------------------------------
1           name1   NULL                type1               NULL
2           name2   NULL                type2               NULL
3           name3   2                   type3               type2
4           name4   NULL                type4               NULL
5           name5   NULL                type5               NULL
6           name6   7                   type6               type7
6           name6   8                   type6               type8
7           name7   8                   type7               type8
8           name8   7                   type8               type7
9           name9   8                   type9               type8

在我的测试中，我可以构建一个显示前四个列的视图，但我不知道如何获取最后一列"linkedRecordTypeName"。

谢谢

Answer 1

试试这个：

CREATE TABLE #type(
    name    VARCHAR(20),
    typeId  INT
)
INSERT INTO #type VALUES
('type1', 1), ('type2', 2), ('type3', 3),
('type4', 4), ('type5', 5), ('type6', 6),
('type7', 7), ('type8', 8), ('type9', 9);
CREATE TABLE #record(
    recordId    INT,
    name        VARCHAR(20),
    typeId      INT
)
INSERT INTO #record VALUES
(1, 'name1', 1), (2, 'name2', 2), (3, 'name3', 3),
(4, 'name4', 4), (5, 'name5', 5), (6, 'name6', 6),
(7, 'name7', 7), (8, 'name8', 8), (9, 'name9', 9);
CREATE TABLE #record_link(
    recordId        INT,
    recordLinkedId  INT
)
INSERT INTO #record_link VALUES
(9, 8), (8, 7), (7, 8),
(6, 8), (6, 7), (3, 2);
SELECT
    r.recordId,
    r.name,
    l.recordLinkedId,
    t.name,
    t2.name
FROM #record r
INNER JOIN #type t
    ON t.typeId = r.typeId
LEFT JOIN #record_link l
    ON l.recordId = r.recordId
LEFT JOIN #record r2
    ON r2.recordId = l.recordLinkedId
LEFT JOIN #type t2
    ON t2.typeId = r2.typeId
ORDER BY 
    r.recordId, l.recordLinkedId

结果

recordId    name                 linkedRecordId recordTypeName       linkedRecordTypeName
----------- -------------------- -------------- -------------------- --------------------
1           name1                NULL           type1                NULL
2           name2                NULL           type2                NULL
3           name3                2              type3                type2
4           name4                NULL           type4                NULL
5           name5                NULL           type5                NULL
6           name6                7              type6                type7
6           name6                8              type6                type8
7           name7                8              type7                type8
8           name8                7              type8                type7
9           name9                8              type9                type8