MySQL 子查询返回超过 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14841945/
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
subquery returns more than 1 row
提问by user2066199
select
disease_name
from
disease
where
disease_id=
(select disease_id from disease_symptom where
disease.disease_id=disease_symptom.disease_id AND
symptom_id=
(select symptom_id from symptom where symptom.symptom_id=disease_symptom.symptom_id
AND symptom_name='fever' OR symptom_name='head ache'))
Gives an error that subquery returns more than one row. what is the cause?
给出子查询返回多行的错误。原因是什么？
回答by bioneuralnet
Your two outer queries are structured to expect a single result from the their subqueries. But the way you have things structured, your subqueries might return more than one result. If you actually wantmore than one result, restructure it like this:
您的两个外部查询的结构是期望来自它们的子查询的单个结果。但是按照您构建事物的方式，您的子查询可能会返回多个结果。如果您确实想要多个结果，请按如下方式重构它：
... where disease_id IN (subquery returning multiple rows...)
Also, subqueries is kill performance, and it's exponentially wosrse for nested subqueries. You might want to look into using INNER JOINinstead.
此外，子查询会降低性能，而且嵌套子查询会呈指数级恶化。您可能想考虑使用INNER JOIN。
回答by UnholyRanger
Breaking your query down, you have
打破你的查询，你有
Main query:
主要查询：
select disease_name from disease where disease_id=
Subquery 1:
子查询 1：
select disease_id from disease_symptom where
disease.disease_id=disease_symptom.disease_id AND
symptom_id=
Sub query 2:
子查询2：
select symptom_id from symptom where symptom.symptom_id=disease_symptom.symptom_id
AND symptom_name='fever' OR symptom_name='head ache'
Since you are using equal signs, the subqueries cannot return multiple items. It looks like sub query 2 has a greater chance to return 2 items due to the ORbeing used. You may wish to try INclause such as WHERE symptom_id IN (sub-query2)with WHERE disease_id IN (sub-query1)
由于您使用等号，子查询不能返回多个项目。看起来子查询 2 由于OR被使用而有更大的机会返回 2 个项目。您可能希望尝试IN条款，例如WHERE symptom_id IN (sub-query2)withWHERE disease_id IN (sub-query1)
