主页 / dba / 问题 / 224762
Accepted
Artashes Khachatryan
Asked: 2018-12-13 04:58:48 +0800 CST

在表中查找相关名称

  • 772

我有一个包含 2 列的表，name 和 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')

我想写一个 UDF，它将获取一个名称并将返回所有相关的名称。如果a与b相关，b与c相关，这意味着a与c相关。

因此参数 'a'、'b'、'c'、'd' 的预期结果必须相同。

select * from my_func('a')

结果必须是

result
------
a
b
c
d

结果是

select * from my_func('e')

一定是

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

2 个回答

  1. Best Answer

    您可以为此使用递归 CTE，前提是您没有超过 100 个关系（因为您不能MAXRECURSION在函数内部添加，所以您必须将它应用于任何引用该函数的查询 -请参见此处的示例).

    第一步是找到所有无父母的父母，然后将其用作递归的锚点。

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

    然后你可以查询：

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

    结果：

    name
----
a
b
c
d

    当然，用循环引用很容易打破这一点。不要那样做。

    • 1

  2. 查询（双向）：

    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

    结果：

     name
e
f
g

    作为表值函数：

    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

)

    测试

    select * from dbo.my_func('b')

    结果：

    name
a
b
c
d

    测试 2

    select * from dbo.my_func('e')

    结果：

    name
e
f
g
    • 0

相关问题