mysql SQL子查询获取错误或比较整个表

cnjp1d6j  于 2023-04-19  发布在  Mysql
关注(0)|答案(1)|浏览(104)

我有三个表(b_jb_j_h_sm_w_j)与时间戳列,我需要比较它们的日期。b_jb_j_h_s的关系是1:N,事情是:我需要将member_id中最旧的b_j_h_s.created_atCURDATE()进行比较,但我得到了一个错误或整个表中最旧的条目。比较大致如下:

(CURDATE(), min(b_j_h_s.created_at)) < 40 and (m_w_j.created_at, b_j.created_at) < 40
(CURDATE(), min(b_j_h_s.created_at)) > 40 and (m_w_j.created_at, b_j.created_at) < 40
(CURDATE(), min(b_j_h_s.created_at)) < 40 and (m_w_j.created_at, b_j.created_at) > 40
(CURDATE(), min(b_j_h_s.created_at)) > 40 and (m_w_j.created_at, b_j.created_at) > 40

这是我的查询

select b_j.member_id as member_id,
    COUNT(b_j_h_s.id) as h_s_count,
    CAST(IFNULL(SUM(b_j_h_s.status), 0) as UNSIGNED) as h_s_status
from `b_j`
inner join `b_j_h_s` on `b_j`.`id` = `b_j_h_s`.`b_j_id`
right join `b_w` on `b_j`.`member_id` = `b_w`.`member_id`
left join `m_j` on `m_j`.`member_id` = `b_j`.`member_id` and `m_j`.`j_id` = 4
left join `b_w_j` on `b_w_j`.`m_j_id` = `m_j`.`id` and `b_w_j`.`m_w_id` = `b_w`.`id`
where (`b_j`.`b_status_id` in (1, 2)
    and `b_j`.`deleted_at` is null
    and (DATEDIFF(b_w_j.created_at, b_j.created_at) < 40
        AND DATEDIFF(CURDATE(), b_j.created_at) = 40
        and DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at) < 40)
    or (DATEDIFF(b_w_j.created_at, b_j.created_at) > 40
        AND DATEDIFF(CURDATE(), b_w_j.created_at) = 30)
        and DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at) < 40)
    or (DATEDIFF(b_w_j.created_at, b_j.created_at) < 40
        AND DATEDIFF(CURDATE(), b_j.created_at) = 40)
        and DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at) > 40)
    or (DATEDIFF(b_w_j.created_at, b_j.created_at) > 40
        AND DATEDIFF(CURDATE(), b_w_j.created_at) = 30)
        and DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at) > 40)
group by `member_id`
having h_s_count != h_s_status

在给出的查询中的问题是select min(b_j_h_s.created_at) as b_j_h_s_created_at) > 40,但我不知道如何使它工作。在我看到的所有示例中,子查询都有数据来过滤它(在我的例子中是member_id),但这里我没有这条信息,这是我试图检索的。
怎么了?
这是我做的小提琴

ovfsdjhp

ovfsdjhp1#

每个DATEDIFF都希望返回当前日期与b_j_h_s的最小日期之间的差值,这都有一个问题。
DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at)应该是这样的:

DATEDIFF(CURDATE(), (select min(b_j_h_s.created_at) from b_j_h_s) )

另外,您的fiddle中缺少了几个表和列,请考虑修改我在演示中共享的查询,因为我将它们从其中删除了。
Here is my demo

相关问题