子查询没有给出输出

问题描述：

我试图运行一个查询，该查询返回一个名为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;

谢谢！因此，如果我理解正确，主查询和子查询中的属性数量必须匹配？如果我保持它的样子？ – rex 2013-02-24 02:51:43

@rex：是的，数量和顺序。这是一列逐列匹配。不过，您需要围绕多列进行圆括号。 – 2013-02-24 02:53:32

子查询在SELECT子句中只能有一列，除非子查询的主查询中有多列用于比较其所选列。有关更多详细信息，请访问http://www.tutorialspoint.com/sql/sql-sub-queries.htm – 2013-02-24 02:53:46

答

您的子查询返回2列。从你的SELECT列表这样的删除COUNT柱：

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 
WHERE doctorID IN 

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

子查询没有给出输出

相关推荐