mysql查询输出将表列反转为行

Question

我需要重新排列 php 中的数据表和输出。下面的查询给出了图像1的输出，如何实现图像2的输出:MySQL 查询:

SELECT
name,
leavetype,
SUM(Stock-Consumed) as Remaining 
from leaverecord
where (leavetype LIKE 'Casual' OR leavetype LIKE 'Earned' or leavetype LIKE 'Sick')
GROUP BY 1, 2

输出

所需输出:

Answer 1

您可以通过条件聚合来做到这一点:

select name,
       sum(case when leavetype = 'casual' then Stock - Consumed else 0 end) as casual,
       sum(case when leavetype = 'earned' then Stock - Consumed else 0 end) as earned,
       sum(case when leavetype = 'sick' then Stock - Consumed else 0 end) as sick
from leaverecord lr
group by name;