比较子查询的计数

Question

是否可以像这样比较子查询计数结果：

SELECT
    c_contact.id,
    c_contact.name,
    c_contact.firstnames,
    c_contact.securityid
FROM c_monitoring 
  JOIN c_contact ON c_contact.securityid = c_monitoring.securityid 
  JOIN c_ln_event_debtor ON c_ln_event_debtor.f_contact = c_contact.id 
  JOIN c_event ON c_event.id = c_ln_event_debtor.f_event
WHERE
  ( 
    SELECT count(c_event.id) 
    FROM c_event 
    WHERE c_event.id = c_ln_event_debtor.f_event
    AND c_event.f_status IN(50,51,52)
  ) = (
    SELECT count(c_event.id)
    FROM c_event
    WHERE c_event.id = c_ln_event_debtor.f_event
  )
GROUP BY
    c_monitoring.securityid

我希望只有当所有引用c_event行都f_status 50、51 或 52 时，才将行设置为结果集。我试过这个没有运气。

Answer 1

我喜欢这种查询的EXISTS子句。在下文中，我们过滤掉f_status不是 50,51,51 的c_events：

SELECT
    c_contact.id,
    c_contact.name,
    c_contact.firstnames,
    c_contact.securityid
FROM c_monitoring 
  JOIN c_contact ON c_contact.securityid = c_monitoring.securityid 
  JOIN c_ln_event_debtor ON c_ln_event_debtor.f_contact = c_contact.id 
  JOIN c_event ON c_event.id = c_ln_event_debtor.f_contact
WHERE NOT EXISTS (
    SELECT 1
        FROM c_event 
        WHERE c_event.id = c_ln_event_debtor.f_event
        AND c_event.f_status NOT IN (50,51,52)
    )
GROUP BY
    c_monitoring.securityid

如果上述仍然产生意外结果，则可以将 EXISTS 加入到c_ln_event_debtor：

SELECT
            c_contact.id,
            c_contact.name,
            c_contact.firstnames,
            c_contact.securityid
        FROM c_monitoring 
          JOIN c_contact ON c_contact.securityid = c_monitoring.securityid 
          JOIN c_ln_event_debtor ON c_ln_event_debtor.f_contact = c_contact.id 
          JOIN c_event ON c_event.id = c_ln_event_debtor.f_event
        WHERE NOT EXISTS (
            SELECT 1
                FROM 
                c_ln_event_debtor d
                JOIN c_event ce ON ce.id = d.f_event
                WHERE d.f_contact = c_contact.id
                AND ce.f_status NOT IN (50,51,52)
            )
        GROUP BY
            c_monitoring.securityid;

编辑：我注意到c_event使用c_ln_event_debtor.f_contact这是故意的吗？