递归选择，它选择基于行的自己的加子值。

Question

我需要在这样的表中选择行：

​

​

选择表中满足这两种条件的所有行：

• 条件1:值列不应与表v中的任何值匹配
• 条件2:没有附加值(在任何级别上，即:子或子子、子等)具有与表v中任何值匹配的值。

表五如下所示：

​

​

示例表的预期结果。是否应该选择/返回行？

• a1:不-条件2
• a2:不-条件2
• a3:不-条件1
• a4:不-条件1
• a5:是-(值在v中不匹配，在v中没有匹配的附加项)
• a6:是-(值在v中不匹配，在v中没有匹配的附加项)
• a7:是-(值在v中不匹配，在v中没有匹配的附加项)
• a8:是-(值在v中不匹配，在v中没有匹配的附加项)

下面是一个sqlfiddle，其中的表与递归函数一起设置，递归函数显示树中的所有行及其级别，但我不知道如何处理：

http://sqlfiddle.com/#!18/736a28/15/0

Answer 1

检查此解决方案：

--------------------------- DDL+DML
drop table if exists a
drop table if exists v
GO

CREATE TABLE a
    ([id] varchar(13), [parentId] varchar(57), [value] varchar(57))
;

CREATE TABLE v
    ([id] varchar(13), [value] varchar(57))
;

INSERT INTO a
    ([id], [parentId], [value])
VALUES
    ('a1', NULL, NULL),
    ('a2', 'a1', NULL),
    ('a3', 'a2', '1'),
    ('a4', NULL, '5'),
    ('a5', 'a1', '8'),
    ('a6', 'a2', NULL),
    ('a7', NULL, NULL),
    ('a8', NULL, '3'),
    ('a9', 'a8', '7')
;

INSERT INTO v
    ([id], [value])
VALUES
    ('v1', '1'),
    ('v2', '5'),
    ('v3', '10'),
    ('v4', '15'),
    ('v5', '20'),
    ('v6', '25'),
    ('v7', '30'),
    ('v8', '35'),
    ('v9', '40')
;

SELECT * FROM a
SELECT * FROM v
GO

-------------------- Solution

WITH MyRecCTE AS(
    SELECT a.id, a.parentId, a.[value], Res = 'NO'
    FROM a
    INNER JOIN v ON a.[value] = v.[value]
    
    UNION ALL

    SELECT 
        a.id, a.parentId, a.[value], Res = 'NO'
    FROM a
    INNER JOIN MyRecCTE c ON c.parentId = a.id
)
SELECT DISTINCT a.id, a.parentId,a.[value], ISNULL(CONVERT(VARCHAR(3),c.Res),'YES')
FROM a
LEFT JOIN MyRecCTE c ON a.id = c.id
ORDER BY id
GO

结果集(符合要求)：

​

​

为了便于讨论，让我们添加另一个行，它将id a8和a9的行引导为"NO“，因为它是a9的子表，并且具有第二个表的值

INSERT INTO a
    ([id], [parentId], [value])
VALUES
    ('a10', 'a9', 35)
GO

测试2结果集(符合预期)

​

​

Answer 2

这有点复杂，但我创建了一个CTE，其中有一个包含祖先和后代组合(传递闭包)的Path的记录。然后，我创建了第二个CTE，从Path的开头提取父id，从Path的末尾提取子代id，并查找后代的值。然后，最后，我查询第二个CTE，并使用NOT EXISTS过滤行。

 WITH tree
    AS 
    ( 
  
        SELECT a.id, a.parentId, a.value,
                CAST('/' + a.id as varchar(1000)) as Path
        FROM a 
      
        UNION ALL

        SELECT a.id, a.parentId, a.value,
                CAST(t.Path + '/' + a.id as varchar(1000)) as Path
        FROM a
        INNER JOIN tree t
            ON Path LIKE '%/' + a.parentId
    ),
    DT
      AS
      (
        SELECT t.Path,
          RIGHT(LEFT(t.Path,3),2) as parent_id,
          RIGHT(t.Path,2) as descendant_id,
          (SELECT q.[value]
           FROM a q
           WHERE q.id = RIGHT(t.Path,2)
          ) as [descendant_value]

        FROM tree t
      )
   SELECT *
   FROM DT dt_outer
   WHERE NOT EXISTS (SELECT 1 FROM DT dt_inner WHERE dt_inner.parent_id = dt_outer.parent_id AND
     dt_inner.descendant_value IN (SELECT [value] FROM v))
   ORDER BY 2,3

我将结果集与副本放在一起，以便更清楚地了解正在发生的事情。您可以使用一个DISTINCT parent_id来获得唯一的ids。

SQL Fiddle