我在PostgreSQL中有路径和标准表。路径具有多个条件,应满足这些条件才能进行选择。
包含(id,name)的路径表,包含(id,key,value,path_id)的标准表
我需要创建一个SQL查询,用于选择满足两个或多个条件输入(例如,30岁和男性性别)的单个路径。我已经尝试了ANY,如下所示。
SELECT p.*, array_agg(c.*) as criteria
FROM paths as p
INNER JOIN criteria as c ON c.path_id = p.id
WHERE (c.key, c.value) = ANY (array[
("age","30"),
("gender","male")
])
GROUP BY p.id但是只要满足其中任何一个条件(whether age = 30 or sex = "male",而不是两者),它就会得到一条路径。我用ALL替换了ANY,但这根本不会返回任何值。
发布于 2019-12-27 17:48:38
如果我没看错的话,您需要将条件移到having子句,而不是where子句。另外,如果您想要满足所有条件的记录,则需要分别检查每个条件:
select p.*, array_agg(c.key || ':' || c.value) as criteria
from paths p
inner join criteria c on c.path_id = p.id
group by p.id
having
count(*) filter(where c.key = 'age' and c.value = '30') > 0
and count(*) filter(where c.key = 'gender' and c.value = 'male') > 0这也可以用两个EXISTS条件来表示,这应该是一个带criteria(path_id, key, value)索引的高效退出查询(但这样就失去了聚合所有条件的能力):
select p.*
from paths p
where
exists (
select 1
from criteria c
where c.path_id = p.id and c.key = 'age' and c.value = '30'
)
and exists (
select 1
from criteria c
where c.path_id = p.id and c.key = 'gender' and c.value = 'male'
)发布于 2019-12-27 19:56:37
当您处理键/值对时,将条件聚合到JSON对象中并使用Postgres的json函数选择您想要的条件可能会更容易:
select p.*, c.*
from paths as p
join (
select path_id, jsonb_object_agg(key, value) as all_criteria
from criteria
group by path_id
having jsonb_object_agg(key, value) @> '{"age": "30", "gender": "male"}'
) c on c.path_id = p.id https://stackoverflow.com/questions/59497436
复制相似问题