Início / dba / Perguntas / 224762
Accepted
Artashes Khachatryan
Asked: 2018-12-13 04:58:48 +0800 CST

Encontrar nomes relacionados na tabela

  • 772

Eu tenho uma tabela com 2 colunas, name e related_name.

CREATE TABLE _test(
[name] NVARCHAR(50),
related_name NVARCHAR(50)
)
GO

INSERT INTO dbo._test
([name], related_name)
VALUES

('a', 'b'),
('b', 'c'),
('c', 'd'),
('e', 'f'),
('f', 'g')

Eu quero escrever um UDF, que receberá um nome e retornará todos os nomes relacionados. Se a está relacionado com b e b está relacionado com c, isso significa que a está relacionado com c.

Portanto, o resultado esperado para o parâmetro 'a', 'b', 'c', 'd' deve ser o mesmo.

select * from my_func('a')

O resultado deve ser

result
------
a
b
c
d

E o resultado para

select * from my_func('e')

devemos ser

result
------
e
f
g
sql-server

2 respostas

  1. Best Answer

    Você pode usar um CTE recursivo para isso, desde que não tenha mais de 100 relações (já que você não pode adicionar MAXRECURSIONdentro da função, então você teria que aplicá-lo em qualquer consulta que faça referência à função - veja um exemplo aqui ).

    O primeiro passo é encontrar todos os pais sem pai, então usar isso como uma âncora para recursão.

    CREATE FUNCTION dbo.my_func(@input nvarchar(50))
RETURNS TABLE 
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH Parents(name) AS
    (
      -- find parent-less parents:
      SELECT name FROM dbo._test
      EXCEPT 
      SELECT related_name FROM dbo._test
    ),
    children AS
    (
      -- use parents as anchor
      SELECT orig = name, name FROM Parents
      UNION ALL
      SELECT c.orig, related_name 
        FROM dbo._test AS t
        INNER JOIN children AS c
        ON c.name = t.name
    )
    SELECT name 
    FROM children AS o
    -- now filter only to a single, common anchor
    WHERE EXISTS
    (
      SELECT 1 FROM children WHERE name = @input AND orig = o.orig
    )
  );

    Então você pode consultar:

    SELECT name FROM dbo.my_func(N'b');

    Resultados:

    name
----
a
b
c
d

    Claro que é fácil quebrar isso com uma referência circular. Não faça isso.

    • 1

  2. A consulta (nos dois sentidos):

    declare @name nvarchar(50) = N'd'
;WITH CTE AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION
select name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE on cte.related_name = t1.name 
),
 CTE2 AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION 
select  name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE2 on t1.related_name = cte2.name 
)

select DISTINCT name  from cte
UNION
SELECT DISTINCT cte.related_name from cte 
UNION
select DISTINCT name  from cte2
UNION
SELECT DISTINCT cte2.related_name from cte2

    Resultado:

     name
e
f
g

    Como uma função com valor de tabela:

    ALTER function dbo.my_func(@name nvarchar(50))
    returns table
    as

    RETURN
    (WITH CTE AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION
select name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE on cte.related_name = t1.name 
),
 CTE2 AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION 
select  name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE2 on t1.related_name = cte2.name 
)

select DISTINCT name  from cte
UNION
SELECT DISTINCT cte.related_name from cte 
UNION
select DISTINCT name  from cte2
UNION
SELECT DISTINCT cte2.related_name from cte2

)

    Teste

    select * from dbo.my_func('b')

    Resultado:

    name
a
b
c
d

    Teste 2

    select * from dbo.my_func('e')

    Resultado:

    name
e
f
g
    • 0

relate perguntas