以下数据表记录了供应商和供应商所能提供的零件,
现在需要找出能够提供完全相同零件的供应商。
Create table SupParts
(sno char(2) not null,
Pno char(2) not null,
Primary key(sno, pno));
思路:
证明两个集合相同可采用以下两种方法
1. 若A包含于B,同时B包含于A,则可证明A=B
2. 若A并B 等于 A交B ,则可证明A=B
采用第一种的思路的一个解答如下:
select sp1.sno, sp2.sno
from SupParts as sp1
inner join
SupParts as sp2
on sp1.pno = sp2.pno
and sp1.sno < sp2.sno
group by sp1.sno, sp2.sno
having count(*) = (select count(*)
from SupParts as sp3
where sp3.sno = sp1.sno)
and count(*) = (select count(*)
from SupParts as sp4
where sp4.sno = sp2.sno);
如果交集中成对零件的数目与两个集合各自元素的数量相等,则这两个集合相等。