WITH DepartmentSearch(DeptID, DeptParent, DeptName, OuID)
AS
(
-- 找出簽核者所屬部門
SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
FROM tbDepartment d
LEFT JOIN tbEmpDept ed ON ed.DeptID = d.DeptID
INNER JOIN tbFlowApprove fa ON fa.Approver = ed.EmpID
WHERE fa.FlowInsID = @FlowInsID AND fa.FlowAct = @FlowAct AND fa.ActAppendix IS NULL
UNION ALL
-- 遞迴向上找出主管部門
SELECT d.DeptID, d.DeptParent, d.DeptName, d.OuID
FROM tbDepartment d
INNER JOIN DepartmentSearch ON DepartmentSearch.DeptParent = d.DeptID
)
SELECT * FROM DepartmentSearch
other ref:
一般資料表運算式(Common Table Expressions, CTE)
Posts
