create
table
staff(staff_id
int
,staff_name
varchar
(
6
))
--
-建测试表,职工表
go
insert staff
select 1 , ' 小王 ' union all
select 2 , ' 小刘 ' union all
select 3 , ' 赵科长 ' union all
select 4 , ' 吴经理 ' union all
select 5 , ' 董主任 '
go
select * -- -------------显示表记录
from staff
go
create table staff_leader_relation(staff_id int ,leader_id int ) -- --建立员工关系表
alter table staff_leader_relation add constraint pk_relation foreign key (staff_id) references staff(staff_id) -- ---表外建FK
go
alter table staff add constraint pk primary key nonclustered (staff_id) -- -------------- PK
alter table staff_leader_relation add constraint pk2_relation foreign key (leader_id) references staff(staff_id)
go
select *
from staff_leader_relation -- - 此时为空,可先为两个有外键联系的表先建立外键约束,再输入记录
go
insert staff_leader_relation
select 1 , 3 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 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
select distinct 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
order by 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 left join 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 not in ( select staff_id from sl_ralation)
go
insert staff
select 1 , ' 小王 ' union all
select 2 , ' 小刘 ' union all
select 3 , ' 赵科长 ' union all
select 4 , ' 吴经理 ' union all
select 5 , ' 董主任 '
go
select * -- -------------显示表记录
from staff
go
create table staff_leader_relation(staff_id int ,leader_id int ) -- --建立员工关系表
alter table staff_leader_relation add constraint pk_relation foreign key (staff_id) references staff(staff_id) -- ---表外建FK
go
alter table staff add constraint pk primary key nonclustered (staff_id) -- -------------- PK
alter table staff_leader_relation add constraint pk2_relation foreign key (leader_id) references staff(staff_id)
go
select *
from staff_leader_relation -- - 此时为空,可先为两个有外键联系的表先建立外键约束,再输入记录
go
insert staff_leader_relation
select 1 , 3 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 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
select distinct 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
order by 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 left join 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 not in ( select staff_id from sl_ralation)