获取关联记录名称包含字符串且关联记录计数大于阈值的记录 [英] Get records where associated records name contain a string AND associated record count is bigger than threshold

问题描述

我有下表:

houses 
users
custom_values

用户拥有FK待安置(house_id)-房屋有许多用户 一个custom_value包含一个FK来放置(house_id)-一个房子具有许多自定义值

A user has a FK to house (house_id) - A house has many users A custom_value has a FK to house (house_id) - A house has many custom values

我想得到所有(不同的)房屋:

I want to get all the (distinct) houses that:

• 至少具有1个关联的custom_value，其中name列包含字符串"red"(不区分大小写)，并且custom_value列type的值是强制性".
• 至少有100位关联用户，其状态列为活动"

• have at least 1 associated custom_value which name column contains the string 'red' (case insensitive) AND the custom_value column type value is 'mandatory'.
• have at least 100 associated users which status column is 'active'

如何在PostgreSQL中运行此查询?

How can I run this query in PostgreSQL?

推荐答案

您可以使用两个相关的子查询:一个在custom_values上带有exists，另一个在相关的users数量上具有不等式:

You could two correlated subqueries: one with exists on custom_values, the other with a inequality condition on the number of related 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

这篇关于获取关联记录名称包含字符串且关联记录计数大于阈值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！