四张表
T_ORGANIZATION
T_EMPLOYEE_ROLE
T_ROLE
T_EMPLOYEE
T_EMPLOYEE
---------------
ID 员工编号
FIRSTNAME 用户名
LASTNAME 用户名
T_ORGANIZATION
---------------
ID
PARENT_ID
NAME
DESCR 组织全称
T_ROLE
---------------
ID
NAME 角色名称
DESCR
T_EMPLOYEE_ROLE
-------------
ID
EMPLOYEE_ID
ROLE_ID
ORG_ID
select e.ID,(LASTNAME||FIRSTNAME)as EMPNAME,o.NAME as ORGNAME,r.DESCR as ROLENAME from T_EMPLOYEE_ROLE er left join T_EMPLOYEE as e on e.ID= er.EMPLOYEE_ID left join T_ORGANIZATION o on o.ID =er.ORG_ID left join T_ROLE r on r.ID = er.ROLE_ID ORDER BY ID DESC 分清哪个是主表是关键