MYSQL 中不存在多个以检查行数 [英] Multiple not exists in MYSQL to check count of rows
本文介绍了MYSQL 中不存在多个以检查行数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使此查询起作用,这是我的逻辑,我正在尝试选择至少没有以下选项之一的记录:
I am trying to make this query work, here is my logic , I am trying to select record thats there is not at LEAST NOT one of these options:
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 的方法?
how to accomplish this , this is what I have so far I am not sure how to check for COUNT(id) = 1 in conditions 3, 4, and 5?
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()
)
推荐答案
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 使您的 COUNT(id) = 1 条件(如果 outreach_links 中有多个链接的 id,则 cond2 将大于 1)
Not sure if that's you are looking for, but you can try it. cond2 != 1 makes your COUNT(id) = 1 condition (if there are more than one linked id in outreach_links cond2 will be greater than 1)
这篇关于MYSQL 中不存在多个以检查行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文