sql - 使用 CASE 作为可选谓词

Question

有人可以帮我解决这个问题吗？

我是使用 CASE 的新手，我该如何完成这项工作。如果 rights = manager 那么我想在案例中运行代码。

select email, user_id, first_name, last_name, rights
from users u
where company_id = 2141
    and receives_emails = 'y'
    case u.rights when 'manager' then
        and user_id in (select user_id from manager_depts where company_id = u.company_id and dept_id = 2)
    end

谢谢!

Answer 1

您实际上不需要这里的 CASE，

SELECT email, u.user_id, first_name, last_name, rights
FROM users u
     LEFT JOIN manager_depts d ON d.company_id = u.company_id and d.dept_id = 2
WHERE company_id = 2141 AND receives_emails = 'y'
  AND (u.rights != 'manager' OR d.user_id IS NOT NULL)