php - mysql在单个查询中进行多次计数

Question

SELECT SUM( CASE WHEN is_half = 1 THEN 1 ELSE 0 END ) halfCount, SUM( CASE 
WHEN is_half = 0 THEN 1 ELSE 0 END ) totalCount FROM employee_leave WHERE 
YEAR(leave_date_from) = YEAR(CURRENT_DATE()) AND employee_id="EMP12" AND 
leave_status=1

通过此查询，我得到的结果为(如果没有值):

halfCount | totalCount
NULL      | NULL

我怎样才能得到 0 而不是 NULL？

期望的输出:

halfCount | totalCount
0         | 0

Answer 1

假设您的查询是正确的，您可以使用 COALESCE 将 null 更改为 0 (或任何其他数字)

SELECT COALESCE(SUM( CASE WHEN is_half = 1 THEN 1 ELSE 0 END ), 0) as 'halfCount',
COALESCE(SUM( CASE WHEN is_half = 0 THEN 1 ELSE 0 END ), 0) as 'totalCount'
FROM employee_leave
WHERE YEAR(leave_date_from) = YEAR(CURRENT_DATE()
     AND employee_id="EMP12"
     AND leave_status=1

我不确定这是否没有错误，因为我无法在我的机器上对其进行测试，但这就是使用 COALESCE

的方法