我有5个SQL表,其以下各列:
tbl_department:
department_id, parent_id
tbl_employee
employee_id, department_id
tbl_department_manager
department_id, employee_manager_id
tbl_request_regular_employee
request_id, employee_id
tbl_request_special_employee
request_id, employee_id
作为输入数据,我有employee_id
和request_id
。
我需要弄清楚员工是否有权访问该请求(无论他是否是经理)
我们不能在这里使用ORM,因为应用程序的响应能力是我们的优先考虑,并且脚本可能被称为很多。
这是我要实现的逻辑:
tbl_department_manager
根据查询到,employee_id
以检查当前员工是否是经理(该员工也可以是几个部门的经理)。如果是这样,我们得到一个列表department_id
(如果没有找到,则返回false)tbl_department_manager
我们将根据两个表中的和从中查询tbl_request_regular_employee
AND (它们是相同的)tbl_request_special_employee
request_id
employee_id
employee_id
以上收集的信息,我们进行查询以tbl_employee
获取department_id
该员工所属的唯一列表。department_id
p.3中的唯一列表,我们可以将其与p.1中的列表进行比较。tbl_department
部门中,可能会继承我们从p.1获得的部门(所以我们可能需要递归地找到它,parent_id
直到找到至少一个与p中一个元素匹配的项目为止) .1)。如果p.1中的一个元素和p.3中的一个元素之间至少存在一个匹配项,则返回true。因此,需要递归查找。有人可以提供一个提示如何在MSSQL中实现它吗?任何帮助将不胜感激。
declare @employee_id int, @request_id int;
with reqEmployees as (
select regular_employee_id as employee_id
from tbl_request_regular_employee
where request_id = @request_id
union all --concatenate the two tables
select special_employee_id
from tbl_request_special_employee
where request_id = @request_id
),
cte as (
select e.department_id, null as parent_id
from reqEmployees r
join tbl_employee e on e.employee_id = r.employee_id -- get these employees' departments
union all
select d.department_id, d.parent_id
from cte -- recurse the cte
join tbl_department d on d.department_id = cte.parent_id -- and get parent departments
)
-- we only want to know if there is any manager row, so exists is enough
select case when exists (select 1
from cte --join on managers
join tbl_department_manager dm on dm.department_id = cte.department_id
where dm.employee_manager_id = @employee_id)
then 1 else 0 end;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句