MySQL子查询返回多行 [英] MySQL subquery returns more than one row

问题描述

我正在执行以下查询:

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;

我正在尝试使其返回如下内容:

County_Name   Prec_ID   Prec_Name   Voters(Count of # of voters in that precienct)

但是，我得到了错误:

#1242-子查询返回多于1行.

我尝试在子查询中放置COUNT语句，但是出现无效的语法错误.

解决方案

您可以在没有子查询的情况下尝试使用简单的分组方式:

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

使用GROUP BY时，未分组的任何列都必须有一个聚合子句(fe SUM或COUNT.)因此，在这种情况下，您必须对县名称，precienct.id和precient.name进行分组. /p>

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.

I have tried placing the COUNT statement in the subquery but I get an invalid syntax error.

解决方案

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.

这篇关于MySQL子查询返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！