mysql - 在同一查询中组合 MySQL AND 和 OR 语句

Question

这是我当前正在运行的查询:

$result = R::getAll("SELECT * FROM vw_participantes_curso JOIN vw_cursos ON vw_participantes_curso.id = vw_cursos.id_curso where LEFT(vw_cursos.sigla, 3) = 'ICV' AND RIGHT(vw_cursos.sigla, 3) = '312' AND username = :u", 
    array(':u' => $username)
);

用简单的英语，查询是这样的:

Find me a student that is in a course that starts with 'ICV' and ends with '312'.

现在我需要在同一个查询中添加一个 or 语句，如下所示:

Find me a student that is in a course that starts with 'ICV' and ends with '312' and (has roleid 5 or roleid 15).

如何在 MySQL 中像这样格式化嵌套条件？

Answer 1

您可以使用 in 运算符:

SELECT * 
FROM vw_participantes_curso 
JOIN vw_cursos 
ON vw_participantes_curso.id = vw_cursos.id_curso 
where LEFT(vw_cursos.sigla, 3) = 'ICV' 
AND RIGHT(vw_cursos.sigla, 3) = '312' 
AND username = :u
AND roleid in (5,15)

或者如果你真的想要或运算符:

SELECT * 
FROM vw_participantes_curso 
JOIN vw_cursos 
ON vw_participantes_curso.id = vw_cursos.id_curso 
where LEFT(vw_cursos.sigla, 3) = 'ICV' 
AND RIGHT(vw_cursos.sigla, 3) = '312' 
AND username = :u
AND (roleid = 5 or roleid = 15)