您可以使用intersect：

Select * from a inner join b on a.id=b.a_id where status = 'status_1'
intersect 
Select * from a inner join b on a.id=b.a_id where status = 'status_2'

或者，您可以在以下情况下使用：

Select * from a where 
id in 
(select a_id from b where status='status_1')
and id in 
(select a_id from b where status='status_2')

编写查询的直接方法是：

select *
from a
where id in (select a_id from b where b_text = 'status_1')
  and id in (select a_id from b where b_text = 'status_2')
order by id;

这意味着对于每个状态，您必须添加一个带有AND的条件。
如果希望查询不随条件的数量而变化，则必须将所需的状态存储在单独的表中。例如，查询可能看起来像这样：

select *
from a
where not exists
(
  select null
  from desired
  where desired.b_text not in
  (
    select b.b_text
    from b 
    where b.a_id = a.id
  )
)
order by id;

或者，使用逗号分隔的列表代替单独的表（例如'status 1,status 2'）沿着FIND_IN_SET。

set @desired = 'status 1,status 2';

select *
from a
where id in
(
  select a_id
  from b
  where find_in_set (b_text, @desired)
  group by a_id
  having count(*) = (select char_length(@desired) - 
                            char_length(replace(@desired, ',', '')) + 1)
);

演示：https://dbfiddle.uk/0dC-PbEQ

在性能方面，我使用exists和limit 1，因为我发现使用适当的索引，检查的行更少。

insert a values (1,'text 1'),(2,'text 2');
insert b values 
(1,'status 1'),
(1,'status 2'),
(2,'status 1'),
(1,'status 3');

select * 
from a
where exists (select 1 from b 
                where a.id=a_id and b_text='status 1'
                limit 1)
            and
        exists (select 1 from b 
                where a.id=a_id and b_text='status 2'
                limit 1)
;
-- without any index there are 12 rows examined:
Query_time: 0.001135  Lock_time: 0.000587 Rows_sent: 1  Rows_examined: 12 

-- here is the execution plan: 
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
|  3 | DEPENDENT SUBQUERY | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

现在我们为a添加一个索引，为B添加另一个索引：

create index idx on a(id);
create index idx on b(a_id,b_text);

-- after indexing, rows examined are reduced to 5:
Query_time: 0.000934  Lock_time: 0.000596 Rows_sent: 1  Rows_examined: 5

-- here is the execution plan, which is much better as it uses index look-up as its access method and ref as the access type instead of a full table scan:
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL              |    2 |   100.00 | Using where |
|  3 | DEPENDENT SUBQUERY | b     | NULL       | ref  | idx           | idx  | 263     | testdb.a.id,const |    1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ref  | idx           | idx  | 263     | testdb.a.id,const |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+

注意：limit 1子句实际上是多余的，因为EXISTS子查询隐式地覆盖了它。