SQL中关联记录计数的条件 [英] Condition on count of associated records in SQL

问题描述

我有下表（带有给定的列）：

I have the following tables (with given columns):

houses (id)
users (id, house_id, active)
custom_values (name, house_id, type)

我想要全部（不同的）房屋和关联用户的数量：

I want to get all the (distinct) houses and the count of associated users 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?

现在我有此查询（在获取其中关联记录名称包含字符串且关联记录数大于阈值的记录），但我不知道也不知道如何选择用户数（：

Right now I have this query (which was answered in Get records where associated records name contain a string AND associated record count is bigger than threshold), but I don't know how to select the count of users too (:

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

推荐答案

您可以将子查询变成横向联接：

You can turn the subquery to a lateral join:

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

这篇关于SQL中关联记录计数的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！