sql - 查询相交

我正在解决一些关于spj数据库的问题。

S(SNUM, SNAME) [supplier]
P(PNUM, PNAME) [part]
J(JNUM, JNAME) [project]
SPJ(SNUM, PNUM, JNUM, Q)

查询是：获取至少有100个公共部分的项目对。
到目前为止，我已经为它写了一个代码：

SELECT X.JNUM, Y.JNUM FROM SPJ AS X, SPJ AS Y
    WHERE 100 <= (SELECT COUNT(PNUM) FROM (SELECT PNUM FROM SPJ WHERE SPJ.JN=X.JN
                                            INTERSECT
                                           SELECT PNUM FROM SPJ WHERE SPJ.JN=Y.JN));

我认为我的解决方案是正确的。有没有别的办法解决这个问题？

最佳答案

只需获得所有相同的部件组合，然后查看每个项目对的匹配数：

select spj1.jnum as jnum1, spj2.jnum as jnum2
from spj spj1
join spj spj2 on spj1.pnum = spj2.pnum and spj1.jnum < spj2.jnum
group by spj1.jnum, spj2.jnum
having count(distinct spj1.pnum) >= 100;

关于sql - 查询相交，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27171171/