如何在 SQL Server 2000 中编写递归查询 [英] How to write a recursive query in SQL Server 2000

问题描述

我有一个表格，里面有一个看起来像这样的列表

I have a table which has a list which looks like this

参考资料

R. Name    LineNo.       References
  A         1.1           (B,24.1)
  A         6.3           (A, 1.3), (D, 22.1)
  B         23.1          (A. 1.2)
  B         24.1          (B,23.1)
  C         2             (A, 1.1)
  D         3.12          (A, 6.3)

查询应该在记录中一一进行，并根据引用生成一个值，选择第一个让我们说，它是报表名称 A，第 1.1 行，现在引用是 (B, 24.1)，其中意味着我们需要找到报告名称 B，第 24.1 行并选择它的值.在同一个表 R.Name B 和 Line No B 中，24.1 被 (B, 23.1) 引用，所以现在我们需要找到 Report name B，Line No 23.1，我们继续迭代，除非我们找不到引用同一个表，这意味着我们找不到的最后一个在另一个表中具有值.(此表中只找到没有值的引用) ...看下表

The query should go one by one in the records and generate a value based on the references, pick first one lets say, which is Report Name A, Line No. 1.1, Now the reference is (B, 24.1), which means we need to find Report Name B, line no 24.1 and pick its value. In the same table R.Name B and Line No B, 24.1 is referenced by (B, 23.1), So now we need to find Report name B, Line No 23.1, We go on through the iteration unless we cant find a reference in the same table, which means the last one we cant find has a value in another table. (Only References without values are found in this table) ...Look at the table below

表格: GeneratedValues

Table: GeneratedValues

R.Name  LineNo.    Values
----------------------------------------
A       1.2          5632
A       1.3          12.5
A       2.1          25
A       2.2          121
A       2.3          8

现在 A, 1.1 引用 B, 24.1 引用 B, 23.1 引用 A, 1.2 并且由于引用表中不存在 A, 1.2 另一个查询运行并从生成值表中获取数字.在本例中为 5632，因此 A，1.1 = 5632.

Now A, 1.1 References B, 24.1 Which references B, 23.1 Which references A, 1.2 And Since A, 1.2 doesnt exist in the reference table another query runs and fetches the number from the Generated Values table. In this case 5632, Hence A, 1.1 = 5632.

像这样我们一个一个地浏览每条记录.

Like this we go one by one through each record.

我的问题是我不知道如何编写递归查询来实现这一点.

My problem is i dont know how to write a recursive query to implement this.

侯赛因

推荐答案

让我们分解一下.

首先，获得下一个值"的 UDF

Firstly, a UDF to get the next 'value'

CREATE FUNCTION dbo.GetNextReference
(
    @CurrentRef varchar(25)
)
RETURNS varchar(25)
AS
BEGIN
    DECLARE @NextRef varchar(25)
    SELECT @NextRef = [References]
    FROM R
    WHERE '(' + [Name] + ',' + [LineNo] + ')' = @CurrentRef

    RETURN @NextRef
END

下一个找到每个条目的最终值:

Next one to find the final value for each entry :

CREATE FUNCTION dbo.GetFinalReference
(
    @StartRef varchar(25)
)
RETURNS varchar(25)
AS
BEGIN
    DECLARE @NextRef varchar(25), @CurrentRef varchar(25)
    SELECT @NextRef = dbo.GetNextReference(@StartRef), @CurrentRef = @StartRef
    WHILE @NextRef is not null
    BEGIN
        SET @CurrentRef = @NextRef
        SET @NextRef = dbo.GetNextReference(@CurrentRef)
    END

    --at this point @NextRef will be null, so we look in the other table
    DECLARE @FinalValue varchar(25)
    SELECT @FinalValue = [Values]
    FROM GeneratedValues
    WHERE '(' + [Name] + ',' + [LineNo] + ')' = @CurrentRef

    RETURN @FinalValue
END

最后，我们可以运行一个 SELECT

Finally, we can run a SELECT

SELECT [Name], [LineNo], dbo.GetFinalReference([References]) AS [Values]
FROM R

我希望这一切都清楚，而且我没有犯任何语法错误.使用 UDF 可能意味着此查询不会像您希望的那样快速运行，但我认为 UDF 在任何情况下都会对您有用.

I hope this is all clear, and I haven't made any syntactical errors. The use of UDFs may mean this query won't run as quick as you'd like, but I think the UDFs will be useful to you in any case.

这篇关于如何在 SQL Server 2000 中编写递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！