如何修改存储过程查询，展示FunctionScope的父子级关系

我目前有一个用于获取作业表数据的存储过程，当前的查询语句返回的functionscope1和functionscope2是同一个值，现在需要调整逻辑，让functionscope1成为functionscope2的父级，和TBL_FunctionScope表本身的父子层级关系保持一致。

当前的查询语句如下：

SELECT extrajobs.ExtraJobsID, prof.ProfileName, functscope.Name AS functionscope1, functscope.Name AS functionscope2, extrajobs.FunctionScopeID, users.UserID, extrajobs.CreateDate, users.Username, extrajobs.LastUpdate, prof.ProfileName, extrajobs.Description, extrajobs.Day, extrajobs.Hours, extrajobs.Remaining, extrajobs.Coordinator, extrajobs.Feedback__On_Coordinator, extrajobs.Status_Coordinator, extrajobs.Director, extrajobs.Status_Director, extrajobs.Feedback__On_Director 
--SELECT DISTINCT(users.Username), prof.ProfileName 
FROM TBL_User AS users 
JOIN TBL_ExtraJobs AS extrajobs ON extrajobs.UserID = users.UserID 
JOIN TBL_UserFunctionScope AS functscope ON extrajobs.FunctionScopeID= functscope.FunctionScopeID 
JOIN REL_ProfileUser AS relprofileuser ON users.UserID = relprofileuser.UserID 
JOIN TBL_Profile AS prof ON prof.ProfileID = relprofileuser.ProfileID 
JOIN TBL_UserFunction AS funct ON funct.FunctionID = relprofileuser.FunctionID 
WHERE funct.Name = 'Extra-jobs'

请问该如何修改查询来实现这个需求？

要实现这个需求，核心是对TBL_FunctionScope表进行自连接——毕竟我们要从同一张表中同时获取子级（对应当前作业关联的记录）和它的父级记录。

步骤说明 #

• 确认父子字段：先明确TBL_FunctionScope的父子关联字段，通常这类表会有FunctionScopeID（主键）和ParentFunctionScopeID（指向父级的外键），如果你的字段名不同，替换成实际名称即可。
• 添加自连接逻辑：在原查询中两次关联TBL_FunctionScope，一次拿子级（对应functionscope2），一次通过父级ID关联拿父级（对应functionscope1）。

修改后的查询语句 #

SELECT 
    extrajobs.ExtraJobsID, 
    prof.ProfileName, 
    parent_scope.Name AS functionscope1, -- 父级FunctionScope名称
    child_scope.Name AS functionscope2, -- 子级FunctionScope名称
    extrajobs.FunctionScopeID, 
    users.UserID, 
    extrajobs.CreateDate, 
    users.Username, 
    extrajobs.LastUpdate, 
    prof.ProfileName, 
    extrajobs.Description, 
    extrajobs.Day, 
    extrajobs.Hours, 
    extrajobs.Remaining, 
    extrajobs.Coordinator, 
    extrajobs.Feedback__On_Coordinator, 
    extrajobs.Status_Coordinator, 
    extrajobs.Director, 
    extrajobs.Status_Director, 
    extrajobs.Feedback__On_Director 
FROM TBL_User AS users 
JOIN TBL_ExtraJobs AS extrajobs ON extrajobs.UserID = users.UserID 
-- 关联子级：获取当前作业对应的FunctionScope（作为functionscope2）
JOIN TBL_FunctionScope AS child_scope ON extrajobs.FunctionScopeID = child_scope.FunctionScopeID 
-- 关联父级：通过子级的父ID拿到对应的父级记录（作为functionscope1）
LEFT JOIN TBL_FunctionScope AS parent_scope ON child_scope.ParentFunctionScopeID = parent_scope.FunctionScopeID 
JOIN REL_ProfileUser AS relprofileuser ON users.UserID = relprofileuser.UserID 
JOIN TBL_Profile AS prof ON prof.ProfileID = relprofileuser.ProfileID 
JOIN TBL_UserFunction AS funct ON funct.FunctionID = relprofileuser.FunctionID 
WHERE funct.Name = 'Extra-jobs'

关键细节提醒 #

• 用LEFT JOIN关联父级：这样即使某些FunctionScope是顶级节点（没有父级），查询结果依然会返回，此时functionscope1会显示为NULL。如果你的业务要求所有记录必须有父级，可以换成INNER JOIN。
• 字段名适配：如果你的TBL_FunctionScope中父级字段不是ParentFunctionScopeID，记得替换成实际的字段名（比如ParentID）。
• 原查询中的TBL_UserFunctionScope：我这里直接替换成了关联主表TBL_FunctionScope，如果你的业务逻辑需要保留用户与FunctionScope的关联表，可以调整为JOIN TBL_UserFunctionScope AS uf ON extrajobs.FunctionScopeID = uf.FunctionScopeID，再通过uf.FunctionScopeID关联TBL_FunctionScope的父子级。

内容的提问来源于stack exchange，提问作者Pedro Nuno