获取同一表中通过外键连接的所有行

Question

有一个名为tbl_report的表。在这个表中有月度报告，这些报告可以通过它们的reporttid相互连接。

表结构

reportID
year
month
topic
fk_reportID

内容

1  2021  01  Topic10  NULL
2  2021  01  Topic11  NULL
3  2021  02  Topic20  1
4  2021  03  Topic30  3

我将拥有所有通过fk_reporttid直接连接的报告，以及间接连接的报告。

对于给定的示例:

我选择reportID 4，结果应该是:

1  2021  01  Topic10
3  2021  02  Topic20
4  2021  03  Topic30

但是当选择reportID 1时，结果应该是相同的。

谢谢!

Answer 1

我希望有一种更有说服力的方法来做到这一点，但是你要做的基本上是在两个方向上搜索这个表:

reportID→fk_reporttid和fk_reporttid ->reportID .

使用递归CTE可以同时进行搜索，但我认为必须分别进行。因此，以下内容将满足您的要求，但这可能有点笨拙。只需改变@ID的值就可以得到结果:

CREATE TABLE #tmp (reportID int, [year] varchar(5),[month] varchar(3), topic varchar(20), fk_reportID int)
INSERT INTO #tmp VALUES
(1,'2021','01','Topic10',  NULL),
(2,'2021','01','Topic11',  NULL),
(3,'2021','02','Topic20',  1),
(4,'2021','03','Topic30',  3)

DECLARE @nID int = 1
CREATE TABLE #FirstRun (reportID int, [year] varchar(5),[month] varchar(3), topic varchar(20), fk_reportID int)
;WITH CTE as
(
SELECT * FROM #tmp
WHERE reportID = @nID
UNION ALL
SELECT t1.* FROM #tmp t1 
INNER JOIN CTE on t1.reportID = cte.fk_reportID
)
INSERT INTO #FirstRun(reportID,[year],[month],topic,fk_reportID)
SELECT reportID,[year],[month],topic,fk_reportID from CTE
;WITH SecondRun as
(
SELECT * from #tmp
WHERE reportID = @nID
UNION ALL
SELECT t1.* from #tmp t1 INNER JOIN SecondRun SR on t1.fk_reportID = SR.reportID
)
SELECT reportID,[year],[month],topic 
FROM SecondRun
UNION
select reportID,[year],[month],topic from #FirstRun