SQL:查找缺少的层次结构元素链接

Question

我有一个系统，它包括一个关键字的父子表（cols:ID|Keyword|ParentID）、一个事件表和一个将关键字链接到事件的表（cols:EventID|Keyword ID）。

当一个关键字被添加到事件中时，UI中的业务逻辑将为所选关键字以及该关键字的任何父关键字向链接表中添加一个条目，因此层次结构的第三级关键字将在链接表中生成三个条目。

不过，在某些情况下，导入数据时只链接关键字，关键字可以位于父子表中定义的层次结构中的任何级别。也就是说，只有一条记录被添加到链接表中。

是否有一种方法只使用SQL查询（不使用存储的proc），可以识别未链接的层次结构元素并将适当的记录插入链接表？

SQLFiddle中的示例表(http://sqlfiddle.com/#！6/c13c5）：

CREATE TABLE Keywords
    ([ID] int, [Keyword] varchar(9), [ParentID] varchar(4))
;
INSERT INTO Keywords
    ([ID], [Keyword], [ParentID])
VALUES
    (1, 'Keyword 1', NULL),
    (2, 'Keyword 2', '1'),
    (3, 'Keyword 3', '1'),
    (4, 'Keyword 4', '2'),
    (5, 'Keyword 5', '3')
;


CREATE TABLE Events
    ([ID] int, [Description] varchar(4))
;
INSERT INTO Events
    ([ID], [Description])
VALUES
    (1, 'Foo'),
    (2, 'Bar'),
    (3, 'Foo2'),
    (4, 'Foo3')
;

CREATE TABLE EventKeywordLink
    ([Event ID] int, [Keyword ID] int)
;
INSERT INTO EventKeywordLink
    ([Event ID], [Keyword ID])
VALUES
    (1, 2),
    (2, 2),
    (2, 3)
;

注意，事件2链接到关键字2&3，但不是1，因此层次结构是不完整的。我需要识别父级未链接的那些记录，并插入适当的记录。

Answer 1

导入数据时：

1. 对于每个关键字，确定它是否存在于关键字表中，如果不将其定义为父关键字（即parentID=NULL）

2. 如果关键字确实存在，请确定最上面的父项。这可以通过递归CTE来完成。

DROP TABLE temp.dbo.#关键字

CREATE TABLE #keywords 
  ( 
     keywordid INT, 
     parentid  INT, 
     keyword   VARCHAR(100) 
  ) 
INSERT INTO #keywords 
VALUES      (1, 
             NULL, 
             'test') 
INSERT INTO #keywords 
VALUES      (2, 
             1, 
             'test_lower') 
INSERT INTO #keywords 
VALUES      (3, 
             1, 
             'test_upper'); 
INSERT INTO #keywords 
VALUES      (4, 
             2, 
             'test_side'); 
go 
WITH c ( parentid) 
     AS (SELECT parentid 
         FROM   #keywords 
         WHERE  keyword = 'test_lower' 
         UNION ALL 
         SELECT k.parentid 
         FROM   #keywords k 
                JOIN c 
                  ON k.keywordid = c.parentid 
         WHERE  k.keywordid <> k.parentid) 
SELECT parentid 'topmost' 
FROM   c 

3） 选择父关键字和子关键字并插入链接表