c# - 创建存储过程以显示具有来自另一列的连接列值的行

Question

我已经存储了这个 SQL Server 数据库

ID  |  TaxDecNo  |  OwnerName  |   PrevTaxDec
----------------------------------------------
1   |  5374      |  John       |   11135
2   |  9864      |  Doe        |   7394
3   |  11135     |  John       |   21784
4   |  7394      |  Doe        |   6872
5   |  21784     |  John       |   NULL
6   |  6872      |  Doe        |   NULL

当我要显示 ID 3

ID  |  TaxDecNo  |  OwnerName  |   PrevTaxDec
----------------------------------------------
1   |  5374      |  John       |   11135
3   |  11135     |  John       |   21784
5   |  21784     |  John       |   NULL

当我要显示 RecordID 2

ID  |  TaxDecNo  |  OwnerName  |   PrevTaxDec
----------------------------------------------
2   |  9864      |  Doe        |   7394
4   |  7394      |  Doe        |   6872
6   |  6872      |  Doe        |   NULL

只有在没有 PrevTaxDec 连接 TaxDec No 值时才停止显示。

我得到了这个解决方案一段时间，但我不知道如何将其转换为数据已插入表的存储过程

DECLARE @ID int = 3;

WITH YourTable AS(
    SELECT V.ID,
           V.TaxDecNo,
           V.PrevTaxDec
    FROM (VALUES(1,5374,11135),  --This value must already inserted in the table
                (2,9864,7394),
                (3,11135,21784),
                (4,7394,6872),
                (5,21784,NULL), --I assume you aren't really mixing datatyoes. 'N/A' can't be inserted into an int column
                (6,6872,NULL))V(ID,TaxDecNo,PrevTaxDec)),
--Solution
rCTEup AS(
    SELECT YT.ID,
           YT.TaxDecNo,
           YT.PrevTaxDec
    FROM YourTable YT
    WHERE YT.ID = @ID
    UNION ALL
    SELECT YT.ID,
           YT.TaxDecNo,
           YT.PrevTaxDec
    FROM rCTEup r
         JOIN YourTable YT ON r.TaxDecNo = YT.PrevTaxDec),
rCTEdown AS(
    SELECT YT.ID,
           YT.TaxDecNo,
           YT.PrevTaxDec
    FROM YourTable YT
    WHERE YT.ID = @ID
    UNION ALL
    SELECT YT.ID,
           YT.TaxDecNo,
           YT.PrevTaxDec
    FROM rCTEdown r
         JOIN YourTable YT ON r.PrevTaxDec = YT.TaxDecNo)
SELECT ID,
       TaxDecNo,
       PrevTaxDec
FROM rCTEup
UNION ALL
SELECT ID,
       TaxDecNo,
       PrevTaxDec
FROM rCTEdown
WHERE ID != @ID; --As it'll be in rCTEup

标签： c#sql-server