mysql8pivot查询应该返回一个非null值

Question

我希望下面的透视查询显示值0而不是null，

SELECT 
pi.employeeId,
pi.Id,
MAX(CASE
WHEN pi.category = 'Repayment' THEN pi.value
WHEN isnull(pi.category) = 1 then 0
-- ELSE 0
END) as 'Repayment',
MAX(CASE
WHEN pi.category = 'Salary' THEN pi.value
ELSE 0
END) as 'Salary',
MAX(CASE
WHEN pi.category = 'Allowance' THEN pi.value
ELSE 0
END) as 'Allowance'
FROM
payData pi
GROUP BY pi.employeeId , pi.Id ;

上述输出为

employeeId     Id      Repayment    Salary  Allowance
121          2           2000     15000     1000
122          2           null     20000     2000

员工id 122没有还款值，因此所需输出为

employeeId     Id      Repayment    Salary  Allowance
121        2         2000       15000     1000
122        2           0        20000     2000

dbfiddle

Answer 1

我认为不需要还款case的第二个分支。如果在类别不可用时想要0，只需else 0:

SELECT 
employeeId,
Id,
MAX(CASE WHEN category = 'Repayment' THEN value ELSE 0 END) as Repayment,
MAX(CASE WHEN category = 'Salary'    THEN value ELSE 0 END) as Salary,
MAX(CASE WHEN category = 'Allowance' THEN value ELSE 0 END) as Allowance
FROM payData pi
GROUP BY employeeId, Id;

注：

• 不要使用单引号作为标识符！它们应仅用于ANSI SQL中指定的文字字符串，并且在所有数据库中都受支持。

• 您有一个单表查询，所以在所有列名前加前缀不是强制性的