Create Table Dept(
DID int Identity Primary Key,
DeptID INT Not Null,
DeptName VarChar(20) Not Null,
PID int Null
)
Insert Into Dept(DeptID,DeptName,PID)
Select 101510,'平洲营业所',1015 Union All
Select 1015,'桂城营业所',1010 Union ALL
Select 201010,'乐从营业所',2010 Union All
Select 101512,'九江营业所',1015 Union ALL
Select 2010,'大良营业所',1010 Union All
Select 101515,'大沥营业所',1015 Union All
Select 1010,'佛山营业所',null
Go
--子查询
Select DeptName 营业所名,(Select DeptName From Dept B Where A.PID=B.DeptID)上级营业所 From Dept A
--自连接
Select A.DeptName 营业所名,B.DeptName 上级营业所 From Dept A Left Join Dept B On A.PID=B.DeptID
Select * From Dept
Go
Drop Table Dept
go