sql - 使用子查询进行比较

Question

这是我要回答的问题:

我有的是:

SELECT DISTINCT a1.acnum
FROM academic a1, academic a2
WHERE a1.deptnum = a2.deptnum
AND a1.acnum <> a2.acnum
AND a1.acnum IN (Select acnum
from interest
group by acnum
having count(acnum) >1);

这是错误的，因为我正在做的是如果 acnum(学术编号)218 与 acnum 217 在同一部门工作，并且与 acnum 199(diff 部门)具有相同的兴趣，那么我将 acnum 218 添加到列表中。

但是，我应该只添加 acnum 218 和 217，如果它们都有相同数量的现场利益的话。

interest table has fieldnum , acnum

Academic table has acnum , deptnum, name

department table has deptnum, deptName

 FIELDNUM           ACNUM DESCRIP                                                                        
------------------ -------------------- 
292                 100  Multiprocessor and Special purpose computer design                               
293                 100  General (HW)                                                                     
293                 197  Computer architecture  



                                                      

输出应该只列出所有学者的编号..但要说清楚一点:

Acnum Deptnum Interest
1        1       g&f
2        1       g&f
3        2        f
4        3        l
5        4       r&l
6        4       r&l

输出应该是:1个2个5个6

Answer 1

未经测试但应该不错

SELECT DISTINCT a1.acnum
FROM academic a1
INNER JOIN academic a2 ON a1.deptnum = a2.deptnum
                          AND 
                          a1.acnum <> a2.acnum
INNER JOIN interest i1 ON a1.acnum=i1.acnum
GROUP BY a1.acnum
HAVING COUNT(i1.acnum)=(SELECT COUNT(*)
                        FROM interest i2
                        WHERE i1.acnum=i2.acnum)