SQL CASE...WHEN 在 Postgresql 的 WHERE 子句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45941233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE...WHEN in WHERE clause in Postgresql
提问by Michu93
My query looks like:
我的查询看起来像：
SELECT *
FROM table
WHERE t1.id_status_notatka_1 = ANY (selected_type)
AND t1.id_status_notatka_2 = ANY (selected_place)
here I would like to add CASE WHEN so my query is:
在这里我想添加 CASE WHEN 所以我的查询是：
SELECT *
FROM table
WHERE t1.id_status_notatka_1 = ANY (selected_type)
AND t1.id_status_notatka_2 = ANY (selected_place)
AND CASE
WHEN t2.id_bank = 12 THEN t1.id_status_notatka_4 = ANY (selected_effect)
END
but it doesn't work. The syntax is good but it fails in searching for anything. So my question is - how use CASE WHEN in WHERE clause. Short example: if a=0 then add some condition to WHERE (AND condition), if it's not then don't add (AND condition)
但它不起作用。语法很好，但它无法搜索任何内容。所以我的问题是 - 如何在 WHERE 子句中使用 CASE WHEN。简短示例：如果 a=0，则向 WHERE（AND 条件）添加一些条件，如果不是，则不添加（AND 条件）
回答by sagi
No need for CASE EXPRESSION, simply use ORwith parenthesis :
不需要CASE EXPRESSION，只需使用OR括号：
AND (t2.id_bank <> 12 OR t1.id_status_notatka_4 = ANY (selected_effect))
回答by Mo Chan
The accepted answer works, but I'd like to share input for those who are looking for a different answer. Thanks to sagi, I've come up with the following query, but I'd like to give a test case as well.
接受的答案有效，但我想为那些正在寻找不同答案的人分享意见。感谢 sagi，我提出了以下查询，但我也想提供一个测试用例。
Let us assume this is the structure of our table
让我们假设这是我们表的结构
tbl
id | type | status
-----------------------
1 | Student | t
2 | Employee | f
3 | Employee | t
4 | Student | f
and we want to select all Student rows, that have Status = 't', however, We also like to retrieve all Employee rows regardless of its Status.
我们想要选择所有学生行，状态 = 't'，但是，我们也想检索所有员工行，而不管其状态如何。
if we perform SELECT * FROM tbl WHERE type = 'Student' AND status = 't'we would only get the following result, we won't be able to fetch Employees
如果我们执行，SELECT * FROM tbl WHERE type = 'Student' AND status = 't'我们只会得到以下结果，我们将无法获取员工
tbl
id | type | status
-----------------------
1 | Student | t
and performing SELECT * FROM tbl WHERE Status = 't'we would only get the following result, we got an Employee Row on the result but there are Employee Rows that were not included on the result set, one could argue that performing INmight work, however, it will give the same result set. SELECT * FROM tbl WHERE type IN('Student', 'Employee') AND status = 't'
并且执行SELECT * FROM tbl WHERE Status = 't'我们只会得到以下结果，我们在结果上得到了一个 Employee Row 但是结果集中没有包含 Employee Row，有人可能会争辩说执行IN可能有效，但是，它会给出相同的结果集。SELECT * FROM tbl WHERE type IN('Student', 'Employee') AND status = 't'
tbl
id | type | status
-----------------------
1 | Student | t
3 | Employee | t
remember, we want to retrieve all Employee rows regardless of its Status, to do that we perform the query
记住，我们想要检索所有 Employee 行，而不管其状态如何，为此我们执行查询
SELECT * FROM tbl WHERE (type = 'Student' AND status = 't') OR (type = 'Employee')
result will be
结果将是
table
id | type | status
-----------------------
1 | Student | t
2 | Employee | f
3 | Employee | t
