如何使“空”数据表示为0而不是“空”？请参阅下面的查询和屏幕截图：）
SELECT Supervisor,
SUM(CASE WHEN DAYOFWEEK(workdate) = 2 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Monday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 3 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Tuesday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 4 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Wednesday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 5 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Thursday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 6 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Friday`,
SUM(CASE WHEN DAYOFWEEK(workdate) = 7 THEN (case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours * `base rate`) end) END) `Saturday`
JOIN payroll.employeehours ON employeedatanew_copy.`ID Number` = employeehours.employeeid
WHERE employeehours.workdate BETWEEN '$staticstart' AND '$staticfinish'
GROUP BY supervisor
最佳答案
使用COALESCE()
返回列表中的第一个非NULL值，如果没有非NULL值，则返回NULL。
COALESCE(employeehours.employeehours * `base rate`, 0)