mysql - 有没有办法检查值是否为零并在 mysql 中执行此操作或执行此操作

Question

我想做一个sql查询

    SELECT * from members m where abs(due/30*service_charge)>='0' 
AND abs(due/30*service_charge)<='200' group by id

但在某些情况下 service_charge 的值会是 0 ，所以我想写成 if service_charge = 0 , then service_charge = 1;

有办法吗？

Answer 1

使用大小写:

select *
from members m
where abs(due/30*(case when service_charge = 0 then 1 else service_charge end) >= 0 and
      abs(due/30*(case when service_charge = 0 then 1 else service_charge end)<= 200
group by id;

但是，这通常使用 NULLIF() 来处理——这意味着 NULL 将从计算中返回:

select *
from members m
where abs(due / 30 * nullif(service_charge, 0) >= 0 and
      abs(due / 30 * nullif(service_charge, 0) <= 200
group by id;

此外，不要在数字常量周围使用单引号。这会使人感到困惑，并可能使 SQL 优化器感到困惑。