create table sys_users(id integer primary key,lgoinname varchar2(20),real_name varchar2(20));--人员表
create table sys_Departments(department_id integer primary key,parment_id integer,department_name varchar2(20));--部门表
create table sys_Departments_users(id integer primary key,department_id integer ,user_id integer);--人员部门对应表
insert into sys_users(1,'aa','啊啊');
insert into sys_users(2,'bb','版本');
insert into sys_users(3,'cc','存储');
insert into sys_users(4,'dd','到底');
insert into sys_Departments(1,1,'根目录');
insert into sys_Departments(2,1,'中国移动');
insert into sys_Departments(3,1,'中国联通');
insert into sys_Departments(4,2,'北京移动');
insert into sys_Departments(5,4,'海淀移动');
insert into sys_Departments_users(1,1,1);
insert into sys_Departments_users(1,2,1);
insert into sys_Departments_users(1,2,3);
insert into sys_Departments_users(1,3,2);
insert into sys_Departments_users(1,4,3);
insert into sys_Departments_users(1,5,4);
select tt.loginname as loginname,tt.real_Name as real_Name,tt.dept as dept,tt.super as super,tt.junior as junior
from (SELECT a.loginname as loginname,a.real_Name as real_Name,c.department_name as dept,d.department_name as super,
wm_concat(e.department_name) as junior from Sys_Users a --人员表left join Sys_Departments_Users b --人员部门对应表
on a.id = b.user_id left join Sys_Departments c --部门表
on b.department_id = c.department_id left join Sys_Departments d on c.parent_id = d.department_id left join Sys_Departments e
on b.department_id = e.parent_id where 1=1 group by a.loginname, a.real_Name, c.department_name, d.department_name ) tt,
sys_users u
where tt.loginname=u.loginname and tt.dept is not null
oracle 字符串拼接函数
最新推荐文章于 2024-08-19 22:55:20 发布