如何以分层形式选择一个表中的行？

Let us suppose that I have 3 levels of users in my system, one for the ordinary staffs, the second one is for the two supervisors and the last one if for the president which is the highest. Supervisors are the approvers of ordinary staff whenever they are sending request on the system, while the president can approve requests of supervisors and ordinary staffs whenever they are sending request related to the company.

让我们假设我的系统中有3个级别的用户,一个用于普通员工,第二个用于两个主管,最后一个用于*别的总统。监督员是普通员工在系统发送请求时的批准者,而总统可以在发送与公司有关的请求时批准监管人员和普通员工的请求。

Suppose that I have a table employee_information_tbl, and the values for the column, job_pos_status are 5 = ordinary staff, 6= supervisors and 7 = president.

假设我有一个表employee_information_tbl,并且该列的值,job_pos_status是5 =普通​​员工,6 =主管,7 =总统。

id name job_pos_status approver_id

id name job_pos_status approver_id

-----------------------------------------------------------------------------

001 John Peter 7 001

001 John Peter 7 001

002 Anne Sy 6 001

002 Anne Sy 6 001

003 Abigail Sam 6 001

003 Abigail Sam 6 001

004 Paul Top 5 002

004 Paul Top 5 002

005 Lenny Bots 5 003

005 Lenny Bots 5 003

006 Steve Max 5 002

006 Steve Max 5 002

007 Max Collins 5 003

007 Max Collins 5 003

008 Anne Dy 5 003

008 Anne Dy 5 003

009 Maine Mendoza 5 002

009 Maine Mendoza 5 002

Question:
How can I select all the records (that is accessible by the president) without using all query, but a logical one that follows the hierarchy of their organization?

问题:如何在不使用所有查询的情况下选择所有记录(总统可以访问),但是遵循其组织层次结构的逻辑记录?

1 个解决方案

#1

You want the query based on hierarchy, please check this :


create table employee_information_tbl (id nvarchar(3), name nvarchar(50), job_pos_status int, approver_id nvarchar(3) )

insert into employee_information_tbl
select '001', 'John Peter', 7, '001'
union all
select '002', 'Anne Sy', 6, '001'
union all
select '003', 'Abigail Sam', 6, '001'
union all
select '004', 'Paul Top', 5, '002'
union all
select '005', 'Lenny Bots', 5, '003'
union all
select '006', 'Steve Max', 5, '002'
union all
select '007', 'Max Collins', 5, '003'
union all
select '008',' Anne Dy', 5, '003'
union all
select '009', 'Maine Mendoza', 5, '002'

select  a.id, a.name, 
        a.job_pos_status, 
        a.approver_id,
        a.approver_id 
from    employee_information_tbl a 
        Inner join  (select distinct job_pos_status 
                    from employee_information_tbl 
                    ) b
        on b.job_pos_status = a.job_pos_status
order by b.job_pos_status desc, 
         a.approver_id, 
         a.id

drop table employee_information_tbl