我有三个表(b_j
,b_j_h_s
和m_w_j
)与时间戳列,我需要比较它们的日期。b_j
和b_j_h_s
的关系是1:N,事情是:我需要将member_id中最旧的b_j_h_s.created_at
与CURDATE()
进行比较,但我得到了一个错误或整个表中最旧的条目。比较大致如下:
(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
),但这里我没有这条信息,这是我试图检索的。
怎么了?
这是我做的小提琴
1条答案
按热度按时间ovfsdjhp1#
每个
DATEDIFF
都希望返回当前日期与b_j_h_s
的最小日期之间的差值,这都有一个问题。DATEDIFF(CURDATE(), select min(b_j_h_s.created_at) as b_j_h_s_created_at)
应该是这样的:另外,您的fiddle中缺少了几个表和列,请考虑修改我在演示中共享的查询,因为我将它们从其中删除了。
Here is my demo