“子查询返回多于1行"的解决方案错误

2021-11-20 00:00:00 mysql

我有一个返回多行的查询，还有另一个查询，我想在其中将条件设置为来自这些多行的值之一，所以基本上我希望子查询看起来像这样:

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);

其中 multiple row query 返回多行.因此，如果这些行的值是 1、2、3，那么我想将 id 设置为 1 或 2 或 3.

Where multiple row query returns 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.

推荐答案

= 可以在子查询只返回 1 个值时使用.

= can be used when the subquery returns only 1 value.

当子查询返回超过 1 个值时，您将不得不使用 IN:

When subquery returns more than 1 value, you will have to use IN:

select * 
from table
where id IN (multiple row query);

例如:

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

相关文章