MYSQL中不存在多重行数以检查行数

Question

我试图使这个查询工作，这是我的逻辑，我试图选择记录--至少没有这样的选项：

1). status = Approved AND end_date >now
2). status =Approved AND end_date is NULL
3). status = Pending And end_date is NULL & COUNT(id) =1
4). status =Pending AND end_date >now & COUNT(id) =1
5). status =Pending AND end_date <now & COUNT(id) =1

如何做到这一点，到目前为止，我还不确定如何在条件3、4和5中检查COUNT(id) =1？

SELECT
    *
FROM
    outreach
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            outreach_links
        WHERE
            outreach_links.outreach_id = outreach.id
        AND STATUS = "Approved"
        AND end_date > now()
    )
AND NOT EXISTS (
    SELECT
        *
    FROM
        outreach_links
    WHERE
        outreach_links.outreach_id = outreach.id
    AND STATUS = "Approved"
    AND end_date IS NULL
)
AND NOT EXISTS (
    SELECT
        *
    FROM
        outreach_links
    WHERE
        outreach_links.outreach_id = outreach.id
    AND STATUS = "Pending"
    AND end_date > now()
)
AND NOT EXISTS (
    SELECT
        *
    FROM
        outreach_links
    WHERE
        outreach_links.outreach_id = outreach.id
    AND STATUS = "Pending"
    AND end_date IS NULL
)
AND NOT EXISTS (
    SELECT
        *
    FROM
        outreach_links
    WHERE
        outreach_links.outreach_id = outreach.id
    AND STATUS = "Pending"
    AND end_date < now()
)

Answer 1

select
    o.*,
    SUM(if(ol.status = "Approved" and (ol.end_date > now() or end_date is null), 1, 0)) as cond1,
    SUM(if(ol.status = "Pending" and (ol.end_date != now() or end_date is null), 1, 0)) as cond2
from
    outreach o
left join 
    outreach_links ol on ol.outreach_id = o.id
group by
    o.id
having
    cond1 = 0 and cond2 != 1
;

不知道这是不是你要找的，但你可以试试。cond2 != 1使计数( id )=1条件(如果outreach_links cond2中有多个链接id将大于1)