MySQL 子查询返回多于一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/778239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL subquery returns more than one row
提问by gsueagle2008
I am executing this query:
我正在执行这个查询：
SELECT
voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
COUNT((SELECT voterfile_voter.ID
FROM voterfile_voter
JOIN voterfile_household
WHERE voterfile_voter.House_ID = voterfile_household.ID
AND voterfile_household.Precnum = voterfile_precienct.PREC_ID)) AS Voters
FROM voterfile_precienct JOIN voterfile_county
WHERE voterfile_precienct.County_ID = voterfile_County.ID;
I am trying to make it return something like this:
我试图让它返回这样的东西：
County_Name Prec_ID Prec_Name Voters(Count of # of voters in that precienct)
However, I am getting the error:
但是，我收到错误：
#1242 - Subquery returns more than 1 row.
#1242 - 子查询返回超过 1 行。
I have tried placing the COUNTstatement in the subquery but I get an invalid syntax error.
我曾尝试将COUNT语句放在子查询中，但出现无效的语法错误。
采纳答案by Andomar
You can try it without the subquery, with a simple group by:
您可以在没有子查询的情况下尝试使用以下简单分组：
SELECT voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
count(voterfile_voter.ID)
FROM voterfile_county
JOIN voterfile_precienct
ON voterfile_precienct.County_ID = voterfile_County.ID
JOIN voterfile_household
ON voterfile_household.Precnum = voterfile_precienct.PREC_ID
JOIN voterfile_voter
ON voterfile_voter.House_ID = voterfile_household.ID
GROUP BY voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name
When you use GROUP BY, any column that you are not grouping on must have an aggregate clause (f.e. SUM or COUNT.) So in this case you have to group on county name, precienct.id and precient.name.
当您使用 GROUP BY 时，您没有分组的任何列都必须有一个聚合子句（fe SUM 或 COUNT）。因此在这种情况下，您必须根据县名、precienct.id 和 precient.name 进行分组。
回答by Sergey
If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg:
如果您收到 error:error no 1242 子查询返回多于一行，请尝试在您的子查询之前放置 ANY 。例如：
This query return error:
此查询返回错误：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
This is good query:
这是一个很好的查询：
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
回答by Jhonny D. Cano -Leftware-
Try this
尝试这个
SELECT
voterfile_county.Name, voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
(SELECT COUNT(voterfile_voter.ID)
FROM voterfile_voter JOIN voterfile_household
WHERE voterfile_voter.House_ID = voterfile_household.ID
AND voterfile_household.Precnum = voterfile_precienct.PREC_ID) as Voters
FROM voterfile_precienct JOIN voterfile_county
ON voterfile_precienct.County_ID = voterfile_County.ID
回答by Arunjith
See the below example and modify your query accordingly.
请参阅以下示例并相应地修改您的查询。
select COUNT(ResultTPLAlias.id) from
(select id from Table_name where .... ) ResultTPLAlias;
