表数据
| line | exclusionflag
+-------+----------------
| aaaaa | false
| bbbbb | false
| ccccc | true
| eeeee | true
| ggggg | false
| iiiii | true
查询:
select *
from table
where line = 'aaaaa' AND exclusionflag = false -> works
select *
from table
where line = 'bbbbb' AND exclusionflag = false -> works
但是如何使下面的查询工作或它的变体?
表中不存在的项。说"ddddd">
select *
from table
where line = 'ddddd' AND exclusionflag = false`
现在什么也不返回,因为没有行ddddd和exclusionflag = false理解!
但是我想返回行'ccccc', 'eeeee'和'iiiii'行
换句话说,'ccccc', 'eeee '和'iiiii'行不包括包含'ccccc', 'eeee '和'iiiii'的结果,但如果我传递任何其他值,它必须返回那行
这可能吗?
查询可以是(带一个不存在的值ddddd
):
with q as (select * from t where line = 'ddddd' not exclusionflag)
select * from q
union all
select * from t where exclusionflag and not exists (select * from q);
结果:
line exclusionflag
------ -------------
ccccc true
eeeee true
iiiii true
但是,如果您使用确实存在的值(bbbbb
)运行相同的查询:
with q as (select * from t where line = 'bbbbb' and not exclusionflag)
select * from q
union all
select * from t where exclusionflag and not exists (select * from q);
你:
line exclusionflag
------ -------------
bbbbb false
参见DB Fiddle的运行示例。