时间戳

首页 > 解决方案 > 如何查询存储过程到父表和子表？

sql-server - 如何查询存储过程到父表和子表？

问题描述

我有一个作业表和一个存储过程来显示每个用户的作业。但是现在，我需要对同一列使用不同的查询。

现在我得到了这个：

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

现在，在存储过程的结果中，我想将 int functioscope1 作为 funtionscope2 的父级，如TBL_FunctionScope.

我怎样才能做到这一点 ？

标签： sql-serverstored-procedures

解决方案

您需要在 TBL_FunctionScope 和 TBL_UserFunctionScope 中包含 JOIN，如下所示：

SELECT 
    extrajobs.ExtraJobsID, prof.ProfileName, 
    functscope.Name AS functionscope1, 
    functscope.Name AS functionscope2, 
    parentfunctscope.Name as parentfunction,
    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
    TBL_FunctionScope as func ON extrajobs.FunctionScopeID= func.FunctionScopeID
LEFT JOIN
    TBL_UserFunctionScope AS parentfunctscope ON func.ParentScopeID = parentfunctscope.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'

推荐阅读