createtable staff(staff_id int,staff_name varchar(6))---建测试表,职工表 go insert staff select1,'小王'unionall select2,'小刘'unionall select3,'赵科长'unionall select4,'吴经理'unionall select5, '董主任' go select*---------------显示表记录 from staff go createtable staff_leader_relation(staff_id int ,leader_id int )----建立员工关系表 altertable staff_leader_relation addconstraint pk_relation foreignkey(staff_id) references staff(staff_id) -----表外建FK go altertable staff addconstraint pk primarykeynonclustered(staff_id)---------------- PK altertable staff_leader_relation addconstraint pk2_relation foreignkey(leader_id) references staff(staff_id) go select* from staff_leader_relation --- 此时为空,可先为两个有外键联系的表先建立外键约束,再输入记录 go insert staff_leader_relation select1,3unionall select2,3unionall select3,4unionall select5,4 go select* from staff_leader_relation ----展现出----------------------------------------------------------------- staff_name leader_name ---------------------------------------------------------------------------------- select x.staff_name,y.staff_name as leader_name ---------我作的,可显示,但是没有作业领导职工的人显示不出来 from staff x,staff_leader_relation s,staff y where x.staff_id=s.staff_id and s.leader_id=y.staff_id selectdistinct a.staff_name,c.staff_name as leader_name from staff a,staff_leader_relation b,staff c -------- 同上 where a.staff_id=b.staff_id and b.leader_id=c.staff_id orderby a.staff_name select---------------splory给出的完整答案,包括职工吴经理(null) (select staff_name from staff where staff_id=a.staff_id) xiashu ,(select staff_name from staff where staff_id=b.leader_id) lingdao from staff a leftjoin sl_ralation b on a.staff_id=b.staff_id --------------------------------------------- -------------------------------------------- select A.staff_name,B.staff_name -----------中山大学开出答案 from staff A,staff B,sl_ralation C where A.staff_id=C.staff_id and B.staff_id=C.leader_id union select A.staff_name,null from staff A where A.staff_id notin (select staff_id from sl_ralation)