MySQL “子查询返回多于1行”错误的解决方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28171474/
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
Solution to "subquery returns more than 1 row" error
提问by wdc
I have one query that returns multiple rows, and another query in which I want to set criteria to be either one of values from those multiple rows , so basicly I want the subquery to look something like this:
我有一个返回多行的查询，还有另一个查询，我想在其中将条件设置为来自这些多行的值之一，所以基本上我希望子查询看起来像这样：
select *
from table
where id= (multiple row query);
Where multiple row queryreturns multiple rows. So if the values from those rows are 1,2,3 then I want to set id to be 1 or 2 or 3.
哪里multiple row query返回多行。因此，如果这些行的值是 1,2,3，那么我想将 id 设置为 1 或 2 或 3。
回答by Raging Bull
=can be used when the subquery returns only 1 value.
=当子查询仅返回 1 个值时可以使用。
When subquery returns more than 1 value, you will have to use IN:
当子查询返回超过 1 个值时，您必须使用IN：
select *
from table
where id IN (multiple row query);
For example:
例如：
SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students) --Subquery returns only 1 value
SELECT *
FROM Students
WHERE Marks IN
(SELECT Marks
FROM Students
ORDER BY Marks DESC
LIMIT 10) --Subquery returns 10 values
回答by Bohemian
You can use in():
您可以使用in()：
select *
from table
where id in (multiple row query)
or use a join:
或使用连接：
select distinct t.*
from source_of_id_table s
join table t on t.id = s.t_id
where <conditions for source_of_id_table>
The join is never a worse choice for performance, and depending on the exact situation and the database you're using, can give much better performance.
连接从来都不是性能更差的选择，根据具体情况和您使用的数据库，可以提供更好的性能。
回答by John Nico Novero
use MAXin your SELECTto return on value.. EXAMPLE
用于MAX您的SELECT价值回报.. 示例
INSERT INTO school_year_studentid (student_id,syr_id) VALUES
((SELECT MAX(student_id) FROM student), (SELECT MAX(syr_id) FROM school_year))
instead of
代替
INSERT INTO school_year_studentid (student_id,syr_id) VALUES
((SELECT (student_id) FROM student), (SELECT (syr_id) FROM school_year))
try it without MAX it will more than one value
在没有 MAX 的情况下尝试它会超过一个值
回答by user2288580
When one gets the error 'sub-query returns more than 1 row', the database is actually telling you that there is an unresolvable circular reference. It's a bit like using a spreadsheet and saying cell A1 = B1 and then saying B1 = A1. This error is typically associated with a scenario where one needs to have a double nested sub-query. I would recommend you look up a thing called a 'cross-tab query' this is the type of query one normally needs to solve this problem. It's basically an outer join (left or right) nested inside a sub-query or visa versa. One can also solve this problem with a double join (also considered to be a type of cross-tab query) such as below:
当收到错误“子查询返回超过 1 行”时，数据库实际上是在告诉您存在无法解析的循环引用。这有点像使用电子表格并说单元格 A1 = B1，然后说 B1 = A1。此错误通常与需要双重嵌套子查询的场景相关。我建议您查找一种称为“交叉表查询”的内容，这是解决此问题通常需要的查询类型。它基本上是嵌套在子查询中的外部联接（左或右），反之亦然。也可以使用双连接（也被认为是一种交叉表查询）来解决这个问题，如下所示：
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_GET_VEHICLES_IN`(
IN P_email VARCHAR(150),
IN P_credentials VARCHAR(150)
)
BEGIN
DECLARE V_user_id INT(11);
SET V_user_id = (SELECT user_id FROM users WHERE email = P_email AND credentials = P_credentials LIMIT 1);
SELECT vehicles_in.vehicle_id, vehicles_in.make_id, vehicles_in.model_id, vehicles_in.model_year,
vehicles_in.registration, vehicles_in.date_taken, make.make_label, model.model_label
FROM make
LEFT OUTER JOIN vehicles_in ON vehicles_in.make_id = make.make_id
LEFT OUTER JOIN model ON model.make_id = make.make_id AND vehicles_in.model_id = model.model_id
WHERE vehicles_in.user_id = V_user_id;
END
In the code above notice that there are three tables in amongst the SELECT clause and these three tables show up after the FROM clause and after the two LEFT OUTER JOIN clauses, these three tables must be distinct amongst the FROM and LEFT OUTER JOIN clauses to be syntactically correct.
在上面的代码中注意到在 SELECT 子句中有三个表，这三个表出现在 FROM 子句之后和两个 LEFT OUTER JOIN 子句之后，这三个表必须在 FROM 和 LEFT OUTER JOIN 子句中是不同的语法正确。
It is noteworthy that this is a very important construct to know as a developer especially if you're writing periodical report queries and it's probably the most important skill for any complex cross referencing, so all developers should study these constructs (cross-taband double join).
值得注意的是，作为开发人员，这是一个非常重要的结构，特别是如果您正在编写定期报告查询，并且它可能是任何复杂交叉引用的最重要技能，因此所有开发人员都应该研究这些结构（交叉表和双加入）。
Another thing I must warn about is: If you are going to use a cross-tab as a part of a working system and not just a periodical report, you must check the record count and reconfigure the join conditions until the minimum records are returned, otherwise large tables and cross-tabs can grind your server to a halt. Hope this helps.
我必须警告的另一件事是：如果您要将交叉表用作工作系统的一部分而不仅仅是定期报告，则必须检查记录计数并重新配置连接条件，直到返回最少记录，否则，大表和交叉表会使您的服务器停止运行。希望这可以帮助。
