sql - 获取关联记录名称包含字符串且关联记录计数大于阈值的记录

Question

我有以下表格：

houses 
users
custom_values

用户有一个 FK 到房子 (house_id) - 一个房子有很多用户 A custom_value 有一个 FK 到房子 (house_id) - 一个房子有很多自定义值

我想获得所有（独特的）房屋：

• 至少有 1 个关联custom_value哪个name列包含字符串“red”（不区分大小写）并且 custom_value 列type值为“强制”。
• 至少有 100 个关联用户，其状态列为“活动”

如何在 PostgreSQL 中运行此查询？

标签： sqlpostgresqljoinselectgroup-by

Answer 1

您可以使用两个相关的子查询：一个带有existson custom_values，另一个带有不等式条件的相关数量users：

select h.*
from houses
where 
    exists (
        select 1 
        from custom_values cv 
        where cv.house_id = h.house_id and cv.type = 'mandatory' and lower(cv.name) = 'red'
    )
    and (
        select count(*) 
        from users u 
        where u.house_id = h.house_id and u.status = 'active'
    ) >= 100