Query the SQL statements of all upper-level departments

Create Table testdept (
deptid int identity (1,1) primary key,
deptname varchar (16),
superdept int
);
insert into testdept (deptname, superdept) values ('general manager ', 0);
insert into testdept (deptname, superdept) values ('finance Department ', 1);
insert into testdept (deptname, superdept) values ('engineering Department', 1);
insert into testdept (deptname, superdept) values ('accounting Department ', 2);
insert into testdept (deptname, superdept) values ('cashier Department', 2);
insert into testdept (deptname, superdept) values ('Beijing Engineering Office ', 3);
insert into testdept (deptname, superdept) values ('nanjing Engineering Office', 3);

You want to query the result as follows:
Deptid deptname superdept
0. General Manager
1. General Manager of Engineering Department
2. General Manager of Finance Department
3. Finance Department of Accounting Department
4. Cashier Finance Department
5 Beijing Engineering Office Engineering Department
6 Nanjing Engineering Office Engineering Department
 

 

========================================================== ====

 

SelectA. deptid, A. deptname, isuperdept=Isnull(B. deptname, A. deptname)

FromTestdept

LeftJoinTestdept B

OnA. superdept=B. deptid