mysql - MySql 中某行的计算值为空时的情况

Question

我有一个很长的 MySql 字符串，用于计算某个值的每日百分比。但是，如果某一天没有任何内容，它就会跳过这一天并转到下一天。我需要它在通常跳过的那一天吐出“0”。感谢您的帮助!

SELECT day(timestamp), CASE when
round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' iS NULL 
then '0' 
ELSE round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' END 
FROM location.db WHERE timestamp between subdate(curdate(), interval 1 month) 
and curdate() AND (w_comp_current_1+w_comp_current_2) > 45 
GROUP BY MONTH(Timestamp), DAY(Timestamp) 
ORDER BY Timestamp

使用日历表的新查询:

Select date_format(calendar.timestamp,'%b-%e') as 'Month-Day', round(count(w_comp_current_1+W_comp_current_2)*10/86400*100,1) as 'run_time2' from calendar 
Left Join courthouse on calendar.timestamp = courthouse.timestamp 
WHERE calendar.timestamp between subdate(curdate(), interval 1 month) and curdate() and calendar.timestamp > '2013-10-03%'  AND (w_comp_current_1+w_comp_current_2) > 45 
GROUP BY MONTH(calendar.Timestamp), DAY(calendar.Timestamp) ORDER BY calendar.Timestamp

Answer 1

您有两次 case 列的别名，而且两次都出现在错误的位置。它只能在案例的 END 语句之后给出:

SELECT day(TIMESTAMP), CASE 
    WHEN round(count(w_comp_current_1 + W_comp_current_2) * 10 / 86400 * 100, 1) IS NULL 
      THEN '0'
    ELSE round(count(w_comp_current_1 + W_comp_current_2) * 10 / 86400 * 100, 1) 
    END AS 'run_time2' 
FROM location.db
WHERE TIMESTAMP BETWEEN subdate(curdate(), interval 1 month) AND curdate()
  AND (w_comp_current_1 + w_comp_current_2) > 45
GROUP BY MONTH(TIMESTAMP), DAY(TIMESTAMP)
ORDER BY TIMESTAMP