27,580
社区成员
发帖
与我相关
我的任务
分享
with a(deptid,pdeptid,deptname) as
(
select 1,0, 'xxxx有限公司' union all
select 2,1, '市场部' union all
select 3,1,'技术部' union all
select 4,1,'财务部' union all
select 5,1,'综合管理部' union all
select 6,2,'市场调研组' union all
select 7,2,'客服中心' union all
select 8,2,'售后服务组' union all
select 9,3,'技术维护组' union all
select 10,3,'项目维护组' union all
select 11,4,'财务预算组' union all
select 12,4,'出纳组' union all
select 13,5 ,'行政办公室'
)
select a.deptid,b.deptname,a.deptname
from a left join a as b on a.pdeptid=b.deptid
SELECT DeptID, '' AS PDeptName, DeptName FROM dept WHERE pdeptID=0
UNION
SELECT b.DeptID,A.DeptName AS PDeptName,B.DeptName FROM dept A ,dept B WHERE b.PdeptID=a.DeptId
IF OBJECT_ID('dbo.Dept') IS NOT NULL
BEGIN
DROP TABLE dbo.Dept
END
GO
CREATE TABLE dbo.Dept
(
DeptID INT PRIMARY KEY,
PDeptID INT,
DeptName NVARCHAR(50)
)
GO
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 1, 0, 'XXXX有限公司')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 2, 1, '市场部')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 3, 1, '技术部')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 4, 1, '财务部')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 5, 1, '综合管理部')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 6, 2, '市场调研组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 7, 2, '客服中心')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 8, 2, '售后服务组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 9, 3, '技术开发组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 10, 3, '项目维护组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 11, 4, '财务预算组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 12, 4, '出纳组')
INSERT INTO dbo.Dept( DeptID,PDeptID,DeptName ) VALUES( 13, 5, '行政办公室')
SELECT DeptID, '' AS PDeptName, DeptName FROM dbo.dept WHERE pdeptID=0
UNION
SELECT b.DeptID,A.DeptName AS PDeptName,B.DeptName FROM dbo.dept A ,dbo.dept B WHERE b.PdeptID=a.DeptId