我有这些表:
Suppliers S (S#, name-S, Status, City)
PIECES P (P#, name-P, colour, weight, City)
PROJECTS J (J#, name-J, Ciudad)
SELLS SPJ (S#, P#, J#, quantity)
我想找到所有已将"p1"
的供应商卖给了伦敦的所有项目
select PROJECTS.j
from SELLS, Suppliers, PIECES, PROJECTS
where SELLS.s = Suppliers.s
and SELLS .p=PIECES .p
and SELLS .j=PROJECTS .j
and PIECES .p="p1"
and PROYECTOS.ciudad="London"
卖给一个项目的选择供应商,但没有针对所有项目
卖出的数据:
supply piece project quantity
1. s1 p1 j1 5
2. s1 p1 j3 4
3. s2 p1 j2 5
4. s4 p1 j1 1
J1和J2是伦敦的项目,S4不是出售所有预言,而是结果
列的名称甚至不允许别名,因为它会更令人困惑。
无论如何,加入4个表(正确(,将条件设置在where子句中,供应商组和最终条件是在have子句中:
select
suppliers.s, suppliers.name
from suppliers
inner join sells on sells.s = suppliers.s
inner join pieces on pieces.p = sells.p
inner join projects on projects.j = sells.j
where pieces.p = 'p1' and projects.cioudad = 'London'
group by suppliers.s, suppliers.name
having count(distinct projects.j) = (select count(*) from projects where cioudad = 'London')