如何设计SQL递归查询? [英] How to design a SQL recursive query?

问题描述

我将如何重新设计下面的查询，以便它递归地遍历整个树以返回从根到叶的所有后代?(我正在使用 SSMS 2008).我们有一位总统.在他之下是副总裁，然后是高层管理人员等等.我需要返回每个人的名字和头衔.但是这个查询不应该是硬编码的；我需要能够为任何选定的员工运行此程序，而不仅仅是总裁.下面的这个查询是硬编码的方法.

How would I redesign the below query so that it will recursively loop through entire tree to return all descendants from root to leaves? (I'm using SSMS 2008). We have a President at the root. under him are the VPs, then upper management, etc., on down the line. I need to return the names and titles of each. But this query shouldn't be hard-coded; I need to be able to run this for any selected employee, not just the president. This query below is the hard-coded approach.

select P.staff_name [Level1], 
P.job_title [Level1 Title], 
Q.license_number [License 1],
E.staff_name [Level2], 
E.job_title [Level2 Title], 
G.staff_name [Level3], 
G.job_title [Level3 Title]

from staff_view A 
left join staff_site_link_expanded_view P on P.people_id = A.people_id
left join staff_site_link_expanded_view E on E.people_id = C.people_id
left join staff_site_link_expanded_view G on G.people_id = F.people_id
left join facility_view Q on Q.group_profile_id = P.group_profile_id

谢谢，这最符合我的需要.下面是我的 CTE 查询:

Thank you, this was most closely matching what I needed. Here is my CTE query below:

with Employee_Hierarchy (staff_name, job_title, id_number, billing_staff_credentials_code, site_name, group_profile_id, license_number, region_description, people_id)
as
(
    select C.staff_name, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id
    from staff_view A
    left join staff_site_link_expanded_view C on C.people_id = A.people_id
    left join facility_view Q on Q.group_profile_id = C.group_profile_id
    left join regions R on R.regions_id = Q.regions_id
    where A.last_name = 'kromer'
)
select  C.staff_name, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id
from staff_view A
left join staff_site_link_expanded_view C on C.people_id = A.people_id
left join facility_view Q on Q.group_profile_id = C.group_profile_id
left join regions R on R.regions_id = Q.regions_id
WHERE C.STAFF_NAME IS NOT NULL
GROUP BY C.STAFF_NAME, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id
ORDER BY C.STAFF_NAME

但我想知道Employee_Hierarchy"的目的是什么?当我用Employee_Hierarchy"替换外部查询中的staff_view"时，它只返回一条记录=Kromer".那么我们什么时候/哪里可以使用Employee_Hierarchy"?

But I am wondering what is the purpose of the "Employee_Hierarchy"? When I replaced "staff_view" in the outer query with "Employee_Hierarchy", it only returned one record = "Kromer". So when/where can we use "Employee_Hierarchy"?

推荐答案

参见:

• SQL Server - 简单示例递归 CTE
• MSDN:使用公用表表达式的递归查询
• SQL Server 递归 CTE(这看起来很像您正在处理的内容！)

• SQL Server - Simple example of a recursive CTE
• MSDN: Recursive Queries using Common Table Expression
• SQL Server recursive CTE (this seems pretty much like exactly what you are working on!)

更新:

一个合适的递归 CTE 基本上包括三件事:

A proper recursive CTE consist of basically three things:

• 一个锚 SELECT 开始；可以选择例如根级别的员工(其中 Reports_To 为 NULL)，或者它可以选择您定义的任意员工，例如通过参数

• an anchor SELECT to begin with; that can select e.g. the root level employees (where the Reports_To is NULL), or it can select any arbitrary employee that you define, e.g. by a parameter

a UNION ALL

一个递归 SELECT 语句，它从同一个通常自引用的表中进行选择，并与当前正在构建的递归 CTE 连接

a recursive SELECT statement that selects from the same, typically self-referencing table and joins with the recursive CTE being currently built up

这使您能够递归地构建一个结果集，然后您可以从中进行选择.

This gives you the ability to recursively build up a result set that you can then select from.

如果您查看 Northwind 示例数据库，它有一个名为 Employees 的表，它是自引用的:Employees.ReportsTo -->Employees.EmployeeID 定义谁向谁报告.

If you look at the Northwind sample database, it has a table called Employees which is self-referencing: Employees.ReportsTo --> Employees.EmployeeID defines who reports to whom.

您的 CTE 将如下所示:

Your CTE would look something like this:

;WITH RecursiveCTE AS
(
    -- anchor query; get the CEO
    SELECT EmployeeID, FirstName, LastName, Title, 1 AS 'Level', ReportsTo
    FROM dbo.Employees
    WHERE ReportsTo IS NULL

    UNION ALL

    -- recursive part; select next Employees that have ReportsTo -> cte.EmployeeID      
    SELECT 
       e.EmployeeID, e.FirstName, e.LastName, e.Title, 
       cte.Level + 1 AS 'Level', e.ReportsTo
    FROM 
       dbo.Employees e
    INNER JOIN 
       RecursiveCTE cte ON e.ReportsTo = cte.EmployeeID
)
SELECT *
FROM RecursiveCTE
ORDER BY Level, LastName    

我不知道您是否可以将您的示例转换为适当的递归 CTE - 但这基本上是它的要点:锚查询、UNION ALL、递归查询

I don't know if you can translate your sample to a proper recursive CTE - but that's basically the gist of it: anchor query, UNION ALL, recursive query

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