在我的前端,我有一个用于标签的过滤器,它发送给后端一系列字符串,我在数据库中使用了该搜索。
问题是我有五个不同的列,在某些情况下,查询可以返回true(列中有值(,但也可以返回false(列中没有值(,即使用OR
和AND
进行查询动态。
我尝试使用CASE WHEN
,但我没有成功。
我的查询就是这样:
SELECT
*
FROM
table AS cll
WHERE
cll.fill IS NULL
AND
(
(
CASE
WHEN cast(cast(integerField as text) = ANY(ARRAY['15', '']) as boolean) = TRUE
THEN cast(integerField as text) = ANY(ARRAY['15', '2']) AND
ELSE cast(integerField as text) = ANY(ARRAY['15', '2']) OR
END
)
)
//others
但是我有错误:
错误:"结束"或附近的语法错误 ^
有人可以帮助我吗?我不知道为什么..
查询我需要的:
SELECT
*
FROM
table AS cll
WHERE
cll.filled_by IS NULL
AND
cast(integerField as text) = ANY(ARRAY['15', 'existCity']) //TRUE
OR //OR if is false or AND if is true
city = ANY(ARRAY['15', 'existCity']) //TRUE
OR //OR if is false or AND if is true
country = ANY(ARRAY['15', 'existCity']) //FALSE
结果预期:
integerField | city | country
15 'exitsCity' 'Brazil'
未经测试,但是您可以尝试
SELECT
*
FROM
table AS cll
WHERE
cll.filled_by IS NULL
AND ( case
when cast(integerField as text) = ANY(ARRAY['15', 'existCity'])
then cast(integerField as text) = ANY(ARRAY['15', 'existCity'])
and city = ANY(ARRAY['15', 'existCity'])
and country = ANY(ARRAY['15', 'existCity'])
else cast(integerField as text) = ANY(ARRAY['15', 'existCity'])
or city = ANY(ARRAY['15', 'existCity'])
or country = ANY(ARRAY['15', 'existCity'])
end)