SQL server 多表连接二(采用四种方法)

一、表格信息：

二、题目及解答：

1.检索出和职工E1、E3都有联系的北京的供应商信息

法一：full join on连接

select 供应商.供应商号,供应商名,地址
from 供应商 full join 订购单 
on 供应商.供应商号=订购单.供应商号
where 职工号='E1' and 地址='北京' and 供应商.供应商号 
in
(select 供应商号 from 订购单 where 职工号='E3')

法二：in 连接

SELECT * FROM 供应商 WHERE 地址='北京' AND 供应商号 IN
(SELECT 供应商号 FROM 订购单 WHERE 职工号='E1') AND 供应商号 IN
(SELECT 供应商号 FROM 订购单 WHERE 职工号 ='E3')

法三： exists 连接

select * from 供应商 where 地址='北京' and  exists (
select * from 订购单 where 订购单.供应商号=供应商.供应商号 and 职工号='E1') and  exists (
select * from 订购单 where 订购单.供应商号=供应商.供应商号 and 职工号='E3')

法四：自然连接

select 供应商.供应商号,供应商名,地址 
from 供应商,订购单 where 供应商.供应商号=订购单.供应商号 and 职工号='E1'
and 地址='北京'
intersect 
select 供应商.供应商号,供应商名,地址 
from 供应商,订购单 where 供应商.供应商号=订购单.供应商号 and 职工号='E3'
and 地址='北京'

 为了快速写入我就直接上代码段啦

2.检索出和面积最小的仓库有联系的供应商的个数

--in连接
select COUNT(*) as 供应商个数 from 供应商 
where 供应商号 in(select 供应商号 from 订购单 
where 职工号 in(select 职工号 from 职工 
where 仓库号 in(select 仓库号 from 仓库 
where 面积 in(select min(面积) from 仓库))))
--exists连接
select COUNT(*) as 供应商个数 from 供应商 
where exists(select * from 订购单 
where 订购单.供应商号=供应商.供应商号 and exists
(select * from 职工 where 订购单.职工号=职工.职工号 and exists
(select * from 仓库 where 仓库.仓库号=职工.职工号 and exists
(select MIN(面积) from 仓库))))
--自然连接
select COUNT(*) as 供应商个数 from 供应商, 订购单,职工,仓库,(select MIN(面积) A from 仓库) as x 
where 订购单.供应商号=供应商.供应商号 and 订购单.职工号=职工.职工号 and 仓库.仓库号=职工.职工号 and x.A=仓库.面积
--join连接
select count(*) as 供应商个数
from 供应商 join 订购单 on 订购单.供应商号=供应商.供应商号
join 职工 on 订购单.职工号=职工.职工号 join 仓库 on 仓库.仓库号=职工.职工号
 where 面积=(select MIN(面积) from 仓库)

3.  检索出向S4供应商发出订购单的那些仓库的平均面积

--in连接
select AVG(面积) as 平均面积 from 仓库 
where 仓库号 in(
select 仓库号 from 职工 
where 职工号 in(
select 职工号 from 订购单 
where 供应商号='S4') )
--exists连接
select AVG(面积)  as 平均面积 from 仓库
where exists(
select * from 职工 where 职工.仓库号=仓库.仓库号 
and exists(
select * from 订购单 where 订购单.职工号=职工.职工号 and 供应商号='S4'))
--自然连接
select AVG(distinct(面积)) as 平均面积 from 仓库,职工,订购单 
where 仓库.仓库号=职工.仓库号 and 职工.职工号=订购单.职工号
and 供应商号='S4'
--join  on 连接
select AVG(distinct(面积)) as 平均面积 from 仓库 join 职工
on 仓库.仓库号=职工.仓库号 join 订购单 on 订购单.职工号=职工.职工号
and 订购单.供应商号='S4'

4.检索出在上海工作并且只向S6供应商发出了订购单的职工号

select 职工.职工号 from 职工,仓库,订购单 
where  
职工.仓库号=仓库.仓库号 
and 职工.职工号=订购单.职工号
and 城市='上海'
and 供应商号='S6' 
group by 职工.职工号
having COUNT(供应商号) =1

select 职工.职工号 from 职工
join 仓库 on 职工.仓库号=仓库.仓库号 and 城市='上海' 
join 订购单 on 订购单.职工号=职工.职工号
and 供应商号='S6'
group by 职工.职工号
having COUNT(供应商号) =1

select 职工.职工号 from 职工
where 仓库号 in(
select 仓库号 from 仓库 where 职工号 in(
select 职工号 from 订购单 where 供应商号='S6' and 城市='上海'))


select 职工.职工号 from 职工
where exists(
select * from 仓库 where 仓库.仓库号=职工.仓库号 and  exists(
select * from 订购单 where 订购单.职工号=职工.职工号 and 供应商号='S6' and 城市='上海'))