postgresql 获取将ALL值作为参数传递的所有记录

n6lpvg4x  于 2022-11-23  发布在  PostgreSQL
关注(0)|答案(3)|浏览(115)

我有一个属性表:

+----+-----------------------------+
| prop_id | prop_name              |
+---------+------------------------+
| 1       | Cottage                |
+---------+------------------------+
| 2       | Mountain House         |
+---------+------------------------+
| 3       | Beach house            |
+---------+------------------------+

附件表:

+----+-----------------------------+
| acc_id  | acc_name               |
+---------+------------------------+
| GAR     | With garden            |
+---------+------------------------+
| TER     | With terrace           |
+---------+------------------------+
| REN     | Recently renovated     |
+---------+------------------------+

将属性和附件关联起来的表(properties2accessories):

+----+--------------+
| prop_id | acc_id  |
+---------+---------+
| 1       | GAR     |
+---------+---------+
| 1       | REN     |
+---------+---------+
| 2       | GAR     |
+---------+---------+
| 2       | REN     |
+---------+---------+
| 2       | TER     |
+---------+---------+
| 3       | GAR     |
+---------+---------+
| 3       | TER     |
+---------+---------+

我需要所有的属性,有 * 所有 * 的配件,我作为参数传递。
正确示例:
a)带有“花园”和“最近装修”的物业:
我应该得到道具:第1、2页
B)带有“花园”和“露台”的物业:
我应该得到道具:二、三
我试试:

SELECT *
FROM properties2accessories
WHERE acc_id IN ('GAR', 'REN');

但这也有道具3,没有“最近翻新”
我使用Postgres 13
有什么帮助吗?

zf2sa74q

zf2sa74q1#

你可以这样做:

SELECT prop_id from (
 select prop_id, array_agg(acc_id) acc_array
 FROM properties2accessories
 group by prop_id) d
WHERE array['GAR', 'REN'] <@ acc_array;
qnakjoqk

qnakjoqk2#

这就是HAVING可以为您做的,它是一个针对整个组的WHERE条件:

SELECT  prop_id
FROM    properties2accessories
WHERE   acc_id IN ('GAR', 'REN')
GROUP BY prop_id
HAVING  ARRAY_AGG(acc_id) @> ARRAY['GAR', 'REN'];
fwzugrvs

fwzugrvs3#

如果特定ID存在其他条件,则可以使用exists进行检查

SELECT
"prop_id"
FROM properties2accessories p 
WHERE ("acc_id" = 'GAR') 
AND EXISTS (SELECT 1 FROM properties2accessories WHERE "acc_id" = 'REN' AND  "prop_id" = p."prop_id")

| 属性标识|
| - -|
| 一个|
| 2个|

SELECT 2

fiddle

相关问题