mysql - WHERE IN - 必须满足所有参数

Question

我正在执行“Where In”查询。

这是我的查询:

select * from `xh_user` 
where (select count(*) 
       from `xh_roles` 
       inner join `xh_role_user` on `xh_roles`.`id` = `xh_role_user`.`role_id` 
       where `xh_role_user`.`user_id` = `xh_user`.`id` 
       and `title` in (?, ?)) >= 1 
and `id` = ? limit 1'

我正在搜索标题符合我的参数的行。

如果满足其中一个参数，则行返回。

如何才能满足所有参数？

Answer 1

假设您有两个参数:

select *
from `xh_user` 
where (select count(distinct title) 
       from `xh_roles` inner join
            `xh_role_user`
            on `xh_roles`.`id` = `xh_role_user`.`role_id` 
       where `xh_role_user`.`user_id` = `xh_user`.`id` and
             `title` in (?, ?)
      ) = 2 and
      `id` = ?
limit 1;