oracle12g函数拼接,oracle字符串拼接函数

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