我正在尝试运行一个查询,该查询返回在名为Hospital的表中出现3次以上的id列表。到目前为止,我已经想到:

SELECT doctorID
FROM Hospital
WHERE doctorID IN

(SELECT temp.doctorID, count(temp.doctorID) as frequency
FROM Hospital as temp
GROUP BY temp.doctorID
HAVING frequency >3);

但是由于某些原因,我在运行时出错了
Code: 1241. Operand should contain 1 column(s)

当我环顾四周时,似乎是一个普遍的错误,但我找不到一个适用于我的现有解决方案。当我单独运行括号中的查询时,它工作正常。那么为什么它不能作为子查询工作呢?

最佳答案

从子查询中删除第二列,将count()移到HAVING子句中:

SELECT doctorID
FROM Hospital
WHERE doctorID IN

(SELECT temp.doctorID
FROM Hospital AS temp
GROUP BY temp.doctorID
HAVING count(temp.doctorID) > 3);

如果不使用子查询,可以使相同的结果更简单:
SELECT doctorID
FROM   Hospital
GROUP  BY doctorID
HAVING count(doctorID) > 3;

08-06 02:56