DB2的数据库:
部门表的表结构:
DEPARTMENTID VARCHAR(12) 部门编号(主键)
PARENTID VARCHAR(12) 上级部门编号
DEPARTMENTNAME VARCHAR(500) 部门名称
SORTNO INTEGER 部门排序号
用户表的表结构:
USERID INTEGER 用户ID(主键)
USERNAME VARCHAR(100) 用户姓名
DEPARTMENTID VARCHAR(12) 用户所在部门
SORTNO INTEGER 用户排序号
问题:将部门组合成一棵树,并在部门下显示用户,就是说将部门和用户整合成为一棵树。
解决:
从两张表中取出数据,SQL语句如下:
with temp(DEPARTMENTID,PARENTID,DEPTUSERNAME,DEPARTMENTNAME,SORTNO, NODETYPE,USERID)
as (select DEPARTMENTID,PARENTID,DEPARTMENTNAME as DEPTUSERNAME, DEPARTMENTNAME,SORTNO,0,-1 from SYS_T_DEPARTMENT
union
select '-1',a.DEPARTMENTID as PARENTID,a.USERNAME as DEPTUSERNAME,b.DEPARTMENTNAME, a.SORTNO,1,a.USERID
from SYS_T_USER a left join SHERP.SYS_T_DEPARTMENT b on a.DEPARTMENTID=b.DEPARTMENTID)
select ROW_NUMBER() OVER() as ROWID,temp.* from temp order by PARENTID,NODETYPE,SORTNO;
部门表的表结构:
DEPARTMENTID VARCHAR(12) 部门编号(主键)
PARENTID VARCHAR(12) 上级部门编号
DEPARTMENTNAME VARCHAR(500) 部门名称
SORTNO INTEGER 部门排序号
用户表的表结构:
USERID INTEGER 用户ID(主键)
USERNAME VARCHAR(100) 用户姓名
DEPARTMENTID VARCHAR(12) 用户所在部门
SORTNO INTEGER 用户排序号
问题:将部门组合成一棵树,并在部门下显示用户,就是说将部门和用户整合成为一棵树。
解决:
从两张表中取出数据,SQL语句如下:
with temp(DEPARTMENTID,PARENTID,DEPTUSERNAME,DEPARTMENTNAME,SORTNO, NODETYPE,USERID)
as (select DEPARTMENTID,PARENTID,DEPARTMENTNAME as DEPTUSERNAME, DEPARTMENTNAME,SORTNO,0,-1 from SYS_T_DEPARTMENT
union
select '-1',a.DEPARTMENTID as PARENTID,a.USERNAME as DEPTUSERNAME,b.DEPARTMENTNAME, a.SORTNO,1,a.USERID
from SYS_T_USER a left join SHERP.SYS_T_DEPARTMENT b on a.DEPARTMENTID=b.DEPARTMENTID)
select ROW_NUMBER() OVER() as ROWID,temp.* from temp order by PARENTID,NODETYPE,SORTNO;