我在使用我想要执行的mySQL命令进行计算时遇到了一些困难

Question

我用下面的脚本进行了计算，但结果与不同

select claim_by,
count(*) as total_response,
count(response_time>minute(response_time-30)) as total_target,
count(response_time>minute(response_time+30)) as total_untarget
from master_response
where claim_by = 'user'
GROUP BY claim_by

结果：total_response=42，total_target=23，total_untarget=19

对于结果，如果我手动计数，total_target和total_untarget是不同的它应该是total_target=27和total_untarget=15

Answer 1

我希望您的两个计数返回相同的值。如果要计数匹配，请使用sum()而不是count():

select claim_by,
count(*) as total_response,
sum(response_time > minute(response_time - 30)) as total_target,
sum(response_time > minute(response_time + 30)) as total_untarget
from master_response
where claim_by = 'user'
group by claim_by;

count()对非NULL值的数量进行计数。sum()将真值的数量相加。

也就是说，您可能需要<作为其中一个比较。我不确定是哪一个，但我可能会猜测：

sum(response_time < minute(response_time - 30)) as total_target,
sum(response_time > minute(response_time + 30)) as total_untarget

也不清楚您为什么使用minute函数。所以，也许你打算：

sum(response_time < now() - interval 30 minute) as total_target,
sum(response_time > now() + interval 30 minute) as total_untarget