主页 / dba / 问题 / 188357
Accepted
JOSEFtw
Asked: 2017-10-13 11:53:40 +0800 CST

进行遍历特定列的选择的更有效/更清洁的方法？

  • 772

给定以下表结构：

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y

我想做一个“跟随”的选择Target Column，例如：

如果我传入 ID 1，我想返回第 3 行，因为如果您选择目标然后选择下一行，A 将解析为 C，OldUrl = Target 如下所示：

a -> b
b -> c
c -> d

建表SQL：

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

我已经通过使用下面的代码设法做到了这一点，但是我的 SQL 技能非常有限所以我只想知道是否有更好/更清洁的方法来做到这一点？

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id

传入 Id = 1 时需要输出

3   /c  /d
sql-server

2 个回答

  1. Best Answer

    递归 CTE 会为您找到它。通过添加 desc 和 TOP 1，它将寻找最长的可能路径。如果有两条路径可以遵循，它将根据ORDER语句选择一条

    ;WITH follow(idstart, idend, oldurl, target, depth, path) as 
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '->' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;
    • 3

  2. 如果您的音量/硬件/更新频率允许，我很想预先计算一下。它将需要另一列让我们称之为UltimateTarget。随着新行的写入，UltimateTarget 的相应值也会更新。

    第一行到达并写入表中：

    Id  OldUrl  Target  UltimateTarget
1    /a      /b      /b

    然后第二排到了。它被写入并where UltimateTarget = OldUrl更新任何现有行。写完表后的样子：

    Id  OldUrl  Target  UltimateTarget
1    /a      /b      /c
2    /b      /c      /c

    处理完所有数据行后，表为：

    Id  OldUrl  Target  UltimateTarget
1    /a      /b      /d
2    /b      /c      /d
3    /c      /d      /d
4    /x      /y      /y

    删除可能会更棘手，具体取决于链条的交互方式。如果它们都是独立的（A->B->C 和 X->Y->Z），那么很容易 - 只需删除该行并关闭指针。如果链合并（A->B->C->P 和 X->Y->Z->P），它会变得更加复杂。当然，在极端情况下，它们可以从头开始重新计算。

    如果没有预先计算，惰性评估可能是有序的。一旦路径被枚举，它就可以根据起点（和所有中间节点）存储，因此不需要再次执行昂贵的路径遍历。这是否具有成本效益将取决于读/写比率。

    我会顺便指出，这是图形处理的教科书材料。如果可以转移到另一个工具，或者当 SQL Server 的图形处理成熟到足够的水平时，我会建议考虑这种方法。

    • 2

相关问题