MySQL 错误：#1242 - 子查询返回超过 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7867271/
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
error : #1242 - Subquery returns more than 1 row
提问by user705884
I got an error: #1242 - Subquery returns more than 1 row when i run this sql.
我收到一个错误：#1242 - 当我运行这个 sql 时，子查询返回超过 1 行。
CREATE VIEW test
AS
SELECT cc_name,
COUNT() AS total,
(SELECT COUNT(*)
FROM bed
WHERE respatient_id > 0
GROUP BY cc_name) AS occupied_beds,
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY cc_name) AS free_beds
FROM bed
GROUP BY cc_name;
回答by OMG Ponies
The problem is that your subselects are returning more than one value - IE:
问题是您的子选择返回多个值 - 即：
SELECT ...
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY cc_name) AS free_beds,
...
...will return a row for each cc_name, but SQL doesn't support compacting the resultset for the subselect - hence the error.
...将为 each 返回一行cc_name，但 SQL 不支持压缩子选择的结果集 - 因此出现错误。
Don't need the subselects, this can be done using a single pass over the table using:
不需要子选择，这可以使用单次遍历表来完成：
SELECT b.cc_name,
COUNT(*) AS total,
SUM(CASE
WHEN b.respatient_id > 0 THEN 1
ELSE 0
END) AS occupied_beds,
SUM(CASE
WHEN b.respatient_id IS NULL THEN 1
ELSE 0
END) AS free_beds
FROM bed b
GROUP BY b.cc_name
回答by ruakh
This is because your subqueries (the SELECTbits that are inside parentheses) are returning multiple rows for each outer row. The problem is with the GROUP BY; if you want to use subqueries for this, then you need to correlatethem to the outer query, by specifying that they refer to the same cc_nameas the outer query:
这是因为您的子查询（SELECT括号内的位）为每个外行返回多行。问题出在GROUP BY; 如果你想使用子查询这一点，那么你就需要相关的规定，他们指的是相同的他们的外部查询，cc_name因为外部查询：
CREATE VIEW test
AS
SELECT cc_name,
COUNT() AS total,
(SELECT COUNT()
FROM bed
WHERE cc_name = bed_outer.cc_name
AND respatient_id > 0) AS occupied_beds,
(SELECT COUNT(*)
FROM bed
WHERE cc_name = bed_outer.cc_name
WHERE respatient_id IS NULL) AS free_beds
FROM bed AS bed_outer
GROUP BY cc_name;
(See http://en.wikipedia.org/wiki/Correlated_subqueryfor information about correlated subqueries.)
（有关相关子查询的信息，请参阅http://en.wikipedia.org/wiki/Correlated_subquery。）
But, as OMG Ponies and a1ex07 say, you don't actually need to use subqueries for this if you don't want to.
但是，正如 OMG Ponies 和 a1ex07 所说，如果您不想，您实际上不需要为此使用子查询。
回答by a1ex07
Your subqueries return more than 1 row. I think you you need something like :
您的子查询返回超过 1 行。我认为你需要这样的东西：
SELECT COUNT(*) AS total,
COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds
FROM bed
GROUP BY cc_name
You can also try to use WITH ROLLUP+ pivoting (mostly for learning purposes, it's a much longer query ) :
您也可以尝试使用WITH ROLLUP+ 旋转（主要用于学习目的，这是一个更长的查询）：
SELECT cc_name,
MAX(CASE
WHEN num_1 = 1 THEN tot_num END) AS free_beds,
MAX(CASE
WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,
MAX(CASE
WHEN num_1 = IS NULL THEN tot_num END) AS total
FROM
(SELECT cc_name, CASE
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM bed
WHERE
CASE
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name
回答by Soren
SELECT COUNT()
FROM bed
WHERE respatient_id > 0
GROUP BY cc_name
You need to remove the group-by in the sub query, so possibly something like
您需要删除子查询中的分组依据，因此可能类似于
SELECT COUNT(*)
FROM bed
WHERE respatient_id > 0
or possibly -- depending on what your application logic is....
或者可能——取决于你的应用程序逻辑是什么......
SELECT COUNT(*) from (
select count(*),cc_name FROM bed
WHERE respatient_id > 0
GROUP BY cc_name) filterview
