create table T_deptment (
ID int PRIMARY KEY IDENTITY,
DeptNo varchar(20) NOT NULL,
DeptName varchar(100) NOT NULL,
ParentID int NOT NULL,
CreateDate datetime DEFAULT(getdate()),
ModiflyDate datetime DEFAULT(getdate())
)
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('A01','第一级',0);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('B01','第二级01',1);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('B02','第二级02',1);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('C01','第三级01',2);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('C02','第三级02',3);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('C03','第三级03',2);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('D01','第四级01',4);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('D02','第四级02',5);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('D03','第四级03',4);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('D04','第四级04',6);
INSERT INTO T_deptment (DeptNo,DeptName,ParentID) values('D05','第四级05',6);
SELECT * FROM T_deptment
select d.DeptNo as '集团级编号',d.DeptName as '集团级名称',c.DeptNo as '公司级编号',c.DeptName as '公司级名称',
b.DeptNo as '厂处级编号',b.DeptName as '厂处级名称',a.DeptNo as '部级编号',a.DeptName as '部级名称'
from T_deptment a
join T_deptment b on a.ParentID=b.ID
join T_deptment c on b.ParentID=c.ID
join T_deptment d on c.ParentID=d.ID
