递归 SQL Server 查询 [英] Recursive SQL Server query

问题描述

在具有如下结构的表 reviewers 中:

审阅者 |受审者====================2 |13 |24 |35 |4

在函数调用中，我知道 reviewer-id 和 reviewee-id(被审核者要检索的项目的所有者).

我现在正在尝试发送一个查询，该查询迭代审阅者表中的所有条目，从审阅者开始，以审阅者的 ID 结束(并将其与我知道的审阅者 ID 相匹配).所以我试图找出被审核者和审核者之间是否存在联系.

是否可以在单个查询中执行此操作?

解决方案

您可以这样做:

带 CTE作为(选择审稿人，被审稿人从表名WHERE reviewee = @revieweeID联合所有SELECT p.reviewer, p.reviewee从 CTE cINNER JOIN TableName p ON c.reviewee = p.reviewer)选择 *从 CTE;--- 评论者 = @reviewerID;

演示

In a table reviewers with a structure like this:

reviewer | reviewee
===================
2        |      1
3        |      2
4        |      3
5        |      4

In a function call, I know both a reviewer-id and a reviewee-id (the owner of the item the reviewee is looking to retrieve).

I'm now trying to send a query that iterates all the entries in the reviewers table, starting with the reviewer, and ends at the reviewee's id (and matches that to the reviewee id I know). So I'm trying to find out if there is a connection between reviewee and reviewer at all.

Is it possible to do this in a single query?

解决方案

You can do this:

WITH CTE
AS
(
   SELECT reviewer, reviewee
   FROM TableName 
   WHERE reviewee = @revieweeID
   UNION ALL
   SELECT p.reviewer, p.reviewee 
   FROM CTE c
   INNER JOIN TableName p ON c.reviewee = p.reviewer
)
SELECT * 
FROM CTE;
--- WHERE reviewer = @reviewerID;

Demo

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