英文:
Condition to filter records with "in" and "and" operators
问题
| ID | 名称 | 可搜索 |
|---|---|---|
| 1 | foo | 是 |
| 2 | bar | 是 |
| 3 | zar | 否 |
我有一些id，我需要筛选具有可搜索 = 是的记录。
此查询将结果为ID = 1，因为它是可搜索的，但我想对整个结果应用可搜索筛选，而不是逐行筛选。
SELECT *
FROM my_table
WHERE id IN (1, 3)
AND isSearchable = true
所以在这种情况下，我期望没有结果，因为两个记录都应该首先满足可搜索，然后再筛选id。
我尝试过使用子查询等进行实验，但in运算符（或or运算符）似乎无法达到所需结果。
也许有些东西非常简单，但我不知道如何解决。感谢您的帮助。
英文:
| ID | name | isSearchable |
|---|---|---|
| 1 | foo | true |
| 2 | bar | true |
| 3 | zar | false |
I've got some ids and I need to filter records where they have isSearchable = true.
This query give as result ID = 1 because is searchable, but I would to apply the filter isSearchable to the entire result, not row-by-row.
SELECT *
FROM my_table
WHERE id IN (1, 3)
AND isSearchable = true
So in this case I'm expecting no-results because both records should be in first isSearchable and after that, filter the ids.
I've tried experimenting with sub-query etc but the in operator (or the or operator) but I'm not able to accomplish the result.
Maybe is something really simple, but I've no ideas on how to solve.
Thanks for your help.
答案1
得分: 3
使用窗口函数的一种方法：
SELECT ID
FROM (SELECT ID,
MIN(isSearchable::INT) OVER() AS minSearchable
FROM my_table
WHERE id IN (1,3)) cte
WHERE minSearchable = 1
在此处查看演示链接。
英文:
One approach using a window function:
SELECT ID
FROM (SELECT ID,
MIN(isSearchable::INT) OVER() AS minSearchable
FROM my_table
WHERE id IN (1,3)) cte
WHERE minSearchable = 1
Check the demo here.
答案2
得分: 1
获取行1和3，如果它们都不可搜索，则返回零行；对于1和2，返回两行。
英文:
If you write the query as you would formulate the task it would be somehow as follows
with t2 as (
select id, name, is_searcheable
from t
where id in (1,3)
)
select * from t2
where not exists
(select null from t2 where not is_searcheable);
get the rows 1 and 3 if non of them is not searcheable - returns now rows; for 1 and 2 returns two rows.
答案3
得分: 0
这样怎么样？
```sql
SELECT *
FROM (
SELECT *
FROM my_table
WHERE id IN (1,3)
) x
WHERE isSearchable = true;
想象一种通用的编程语言：
jshell> Integer x;
x ==> null
jshell> if(x == 3 || x == null) System.out.println("bad");
| Exception java.lang.NullPointerException: Cannot invoke "java.lang.Integer.intValue()" because "REPL.$JShell$11.x" is null
| at (#2:1)
jshell> if(x == null || x == 3) System.out.println("bad");
bad
筛选顺序可以完全改变结果。如果您明确编写查询以使执行计划不容置疑，那就没问题。
<details>
<summary>英文:</summary>
How about this?
```sql
SELECT *
FROM (
SELECT *
FROM my_table
WHERE id IN (1,3)
) x
WHERE isSearchable = true;
Think of a generic programming language:
jshell> Integer x;
x ==> null
jshell> if(x == 3 || x == null) System.out.println("bad");
| Exception java.lang.NullPointerException: Cannot invoke "java.lang.Integer.intValue()" because "REPL.$JShell$11.x" is null
| at (#2:1)
jshell> if(x == null || x == 3) System.out.println("bad");
bad
The filter order can completely change the results. If you explicitly write the query so that the execution plan is not open to interpretation, you're good.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论