Oracle DB设置运算符以计算重复字段

Question

我很难尝试使用（union/Intersect/minus）来计算这些表中的主要密钥acnum的次数。

我尝试了多种使用集合操作员的方法，但似乎无法弄清楚。

即使是这样的东西似乎也没有用：

SELECT results.ACNUM, COUNT(results.books), COUNT(results.interests)
FROM (
 SELECT ACNUM FROM ACADEMIC A
 UNION
 SELECT COUNT(PANUM) as books FROM AUTHOR B
 UNION
 SELECT COUNT(FIELDNUM) as interests FROM INTEREST C
) results;

基本上，我需要将此查询（有效）转换为上面的查询，我使用集合操作员。

SELECT DISTINCT ACNUM
FROM ACADEMIC A
WHERE
  (SELECT COUNT(PANUM)
  FROM AUTHOR
  WHERE ACNUM = A.ACNUM) < 5
AND
  (SELECT COUNT(FIELDNUM)
  FROM INTEREST
  WHERE ACNUM = A.ACNUM) > 3;

Answer 1

我不知道为什么您坚持使用设置运算符，但Intersect将返回预期的结果：

SELECT ACNUM
FROM ACADEMIC A
INTERSECT
SELECT ACNUM
FROM AUTHOR
GROUP BY ACNUM 
HAVING COUNT(PANUM) < 5
INTERSECT
SELECT ACNUM
FROM INTEREST
GROUP BY ACNUM 
HAVING COUNT(FIELDNUM) > 3;

Answer 2

您已经使用了 union 以错误的方式查询，因为IST查询中的内部查询仅产生了一列，并且您从中取了三列。

您可以在此处使用加入，因此可以使用：

 select a.ACNUM, count(ar.PANUM), count(i.FIELDNUM)
 from ACADEMIC a join AUTHOR ar on a.ACNUM=ar.ACNUM
     join INTEREST i on a.ACNUM=i.ACNUM
 group by a.ACNUM 
 having count(ar.PANUM)<5 and count(i.FIELDNUM)>3;

Answer 3

 SELECT X.ACNUM, Y.books, Z.interests
 FROM (
 (SELECT ACNUM FROM ACADEMIC A)X
 LEFT JOIN
 (SELECT COUNT(PANUM) as books FROM AUTHOR B)Y
 ON X.ACNUM=Y.ACNUM
 LEFT JOIN
 (SELECT COUNT(FIELDNUM) as interests FROM INTEREST C)Z
 ON X.ACNUM=Z.ACNUM ) WHERE Y.books<5 and Z.interests>3;