mysql8中作为结果集头的字段

Question

下面是我的数据片段

empid   CategoryName               Type    value
1      Accommodation Deduction   DEDUCTION  -100.0000
1      Canteen Deduction         DEDUCTION  -200.0000
1      Housing                   ADDITION   3000.0000
1      Transport Allowance       ADDITION   2000.0000

empid将是员工id，数据集中还有其他员工id，但所有人都有类似的数据。

我的目标是在查询结果中以列的形式显示各种薪酬类别，如

Accommodation Deduction   Canteen Deduction   Housing          Transport Allowance
-100.0000                 -200.0000        3000.0000          2000.0000

我正在使用以下查询，

select 
MAX(CASE
WHEN CategoryName = 'Accommodation Deduction' THEN pi.value
ELSE 0
END) AS 'Accommodation Deduction',
MAX(CASE
WHEN CategoryName = 'Canteen Deduction' THEN pi.value
ELSE 0
END) AS 'Canteen Deduction',
MAX(CASE
WHEN CategoryName = 'Housing' THEN pi.value
ELSE 0
END) AS 'Housing',
MAX(CASE
WHEN CategoryName = 'Transport Allowance' THEN pi.value
ELSE 0
END) AS 'Transport Allowance' from Report pi group by empid;

扣除类别显示值为零，不应为

Accommodation Deduction   Canteen Deduction     Housing         Transport Allowance
0.0000               0.0000              3000.0000          2000.0000

dbfiddle

Answer 1

有些列的值为负数，因此条件MAX()返回0，因为CASE语句的ELSE 0分支。

您可以将其转换为MIN()s:

select empid
MIN(CASE WHEN CategoryName = 'Accommodation Deduction' THEN pi.value ELSE 0 END) AS Accommodation_Deduction,
MIN(CASE WHEN CategoryName = 'Canteen Deduction'       THEN pi.value ELSE 0 END) AS Canteen_Deduction,
MAX(CASE WHEN CategoryName = 'Housing'                 THEN pi.value ELSE 0 END) AS Housing,
MAX(CASE WHEN CategoryName = 'Transport Allowance'     THEN pi.value ELSE 0 END) AS Transport_Allowance 
from Report pi 
group by empid;