Mysql 'greater than(>)'查询始终返回 0

Question

我正在使用一个查询，我想在其中显示即将到来的日期的数量。以下查询返回 0，即使存在大于当前日期的日期。请帮我解决这个问题。

SELECT  (case when b.booked_date > cast(now() as date) then sum(1) else sum(0) end) as upcoming_booked_facilities                           
from    svk_apt_book_facilities b   
where   b.customer_id = 1
and b.association_id = 1        
and b.is_active = 1
group   by b.facility_id

Answer 1

您需要对CASE表达式求和才能进行条件聚合：

SELECT
facility_id,
SUM(CASE WHEN booked_date > CURDATE() THEN 1 ELSE 0 END) AS upcoming_booked_facilities
FROM svk_apt_book_facilities  
WHERE
customer_id = 1    AND
association_id = 1 AND
is_active = 1
GROUP BY
facility_id;

您尝试将总和用作CASE表达式的谓词，这可能不是您想要的。 请注意，我也在选择facility_id，因为您是按该列分组的。 如果您希望对整个表进行条件总和，请不要选择或按设施分组。