查找重定向链的开始和结束 [英] Find the start and end of a redirect chain

问题描述

我在 SQL 服务器表中有一个 URL 重定向表，每个重定向都有一个 ID、一个 FromURL 和一个 ToURL 字段.

I have a table of URL redirects in a SQL server table, each redirect has an ID, a FromURL and a ToURL field.

我被要求在表中找到我们有重定向链的位置，以便我们可以用单个重定向替换它们，以便用户只重定向一次而不是多次.

I've been asked to find where we have a chain of redirects in the table so that we can replace them with a single redirect so that users are only redirected once rather than multiple times.

表格示例如下:

如您所见，如果用户访问 URL A，他们将被重定向到 B，然后从 B 重定向到 C，然后从 C 到 D 我们想用从 A 到 D 的单个重定向替换它以加快速度页面加载.

As you can see, if a user visits URL A, they will be redirected to B, then from B to C then from C to D we'd like to replace this with a single redirect from A to D to speed up the page load.

我以为我可以通过递归 CTE 在没有游标的情况下做到这一点，但我完全陷入了困境，我设法找到的最好的方法是使用以下内容找到每个链的开头:

I thought I might be able to do this without cursors with a recursive CTE but I got completely stuck with this, the best I managed to to was find the start of each chain with the following:

SELECT  r.ID ,
        r.FromURL ,
        r.ToURL
FROM    dbo.redirect r
WHERE   fromURL NOT IN ( SELECT ToURL
                         FROM   dbo.redirect r2 )

这给了我链的开始(或根本不在链中的链)，方法是选择 FromURL 没有被任何其他重定向重定向的任何记录.当我尝试完成一些递归 CTE 示例时，我最终得到的只是垃圾数据或达到递归限制.

This gives me the start of the chains (or the ones that aren't in a chain at all) by selecting any records where the FromURL hasn't been redirected by any other redirect. When I tried following through some of the recursive CTE examples, all I ended up with was junk data or hitting the recursion limit.

理想情况下，我想从中获得类似于以下内容的数据:

Ideally what I'd like to get out of this is data similar to the following:

如您所见，重定向链已被替换为一个，因此层次结构中的每个级别现在都直接到达链的末端.

As you can see, the chains of redirects have been replaced with a single one, so every level in the hierarchy now goes directly to the end of the chain.

我只是一名 DBA，他同意为我们的 Web 团队做一些我现在发现完全超出我对 T-SQL 能力的事情，所以如果有人能帮助我，我将不胜感激.

I'm just a DBA who agreed to do something for our web team that I have now found completely out of my ability with T-SQL so if anyone can help me out that would be most appreciated.

推荐答案

一般的解决方案可以搜索:Directed Acyclic Graph"、Traversal"、SQL".hansolav.net/sql/graphs.html#topologicalsorting 有一些不错的信息.

The general solution can be found searching for: "Directed Acyclic Graph", "Traversal", "SQL". hansolav.net/sql/graphs.html#topologicalsorting has some good info.

如果您需要快速回答，这里有一个快速而肮脏的方法.效率不高，需要非循环输入，但对于不熟悉sql的人来说是可读的.

If you need a fast answer, here's a quick-and-dirty method. It's not efficient, and it needs an acyclic input, but it's readable to someone not familiar with sql.

SELECT id, FromUrl, ToUrl
INTO #temp
FROM dbo.redirect

WHILE @@ROWCOUNT > 0
BEGIN
  UPDATE cur
  SET ToUrl = nxt.ToURL
  FROM #temp cur
  INNER JOIN #temp nxt ON (cur.ToURL = nxt.FromURL)
END

SELECT * FROM #temp

或者，使用递归 CTE:

Alternatively, with a recursive CTE:

;WITH cte AS (
  SELECT 1 as redirect_count, id, FromURL, ToUrl
  FROM dbo.redirect
  UNION ALL
  SELECT redirect_count + 1, cur.id, cur.FromURL, nxt.ToURL
  FROM cte cur
  INNER JOIN @t nxt ON (cur.ToURL = nxt.FromURL)
)
SELECT
  t1.id, t2.FromUrl, t2.ToUrl
FROM dbo.redirect t1
CROSS APPLY (
  SELECT TOP 1 FromUrl, ToUrl
  FROM cte
  WHERE id = t1.id
  ORDER BY redirect_count DESC
) t2

这篇关于查找重定向链的开始和结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！