如何将查询结果限制为有2个或更多帐户关联的组织

vqlkdk9b  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(380)

我有下面的代码,它为没有地址关联的子组织(父组织)返回结果。但是,这将返回2000+行,其中许多行有0或1个与其关联的帐户。
我想做的是编辑查询,只显示与子组织关联的帐户数为2或更多的结果。
在我们的数据库中,join语句中与accounts和organizations表相关的主键是:

organizations.id = accounts.organization_id

accounts表上的主键是 accounts.id 我一直想弄明白,但没有用。如果有人可以帮助提供一个解决方案,将结果限制为只有帐户数等于或大于2的子组织。

SELECT DISTINCT
  organizations.id AS org_id,
  organizations.name AS org_name,
  parent.id AS parent_org_id,
  parent.name AS parent_org_name
FROM
    organizations 
  INNER JOIN organizations parent 
    ON
    organizations.parent_org_id = parent.id
  LEFT JOIN addresses sub_addresses 
    ON
    sub_addresses.addressable_type = 'Organization'
    AND sub_addresses.is_current = 1
    AND sub_addresses.addressable_id = organizations.id
  LEFT JOIN addresses p_addresses 
    ON
    p_addresses.addressable_type = 'Organization'
    AND p_addresses.is_current = 1
    AND p_addresses.addressable_id = parent.id
WHERE
  sub_addresses.id is null
ORDER BY
  parent.name, 
  organizations.name
qv7cva1a

qv7cva1a1#

您需要在where子句中再添加一个join或子查询,它将为您提供至少有2个或更多帐户的组织。下面是join版本。

FROM organizations p
INNER JOIN organizations o ON o.parent_org_id = p.id
INNER JOIN (Select a.organization_id,Count(distinct a.id) as num_of_accounts
            FROM accounts a 
            GROUP BY a.organization_id
            HAVING Count(distinct a.id) >= 2) accnts ON accnts.organization_id = o.id

相关问题