mysql - 按月汇总值，在mysql中按语句分组

我对如何将以下语句分为几个月有疑问。
数据库是MYSQL

期望的结果是：

DRNAME, Jan, Feb, Mar, April, May ,June
SISHEN, 0, 0, 100, 250, 450, 500, 0

我得到的结果是：

DRNAME, Jan, Feb, Mar, April, May ,June
SISHEN, 0, 0, 100, 0, 0, 0,0
SISHEN, 0, 0,0,250,0,0,0
SISHEN, 0, 0, 0,0 , 450, 0, 0

查询是

select  drname,
case when month(loaddate) = 1 then sum(drvalue) end as 'Jan',<br>
case when month(loaddate) = 2 then sum(drvalue) end as 'Feb',<br>
case when month(loaddate) = 3 then sum(drvalue) end as 'March',<br>
case when  month(loaddate) = 4 then sum(drvalue) end as 'April',<br>
case when  month(loaddate) = 5 then sum(drvalue) end as 'May',<br>
case when  month(loaddate) = 6 then sum(drvalue) end as 'June'<br>
from tblloadschedule<br>
where cancelloadflag = 'N' and drname like 'sish%'<br>
group by drname,month(loaddate)

最佳答案

您不需要昂贵的案例：

select drname,
   sum(if(month(loaddate) = 1 ,drvalue,0) as 'Jan',
   sum(if(month(loaddate) = 2 , drvalue ,0) as 'Feb',
   sum(if(month(loaddate) = 3 , drvalue ,0) as 'March',
   sum(if(month(loaddate) = 4 , drvalue ,0) as 'April',
   sum(if(month(loaddate) = 5 , drvalue ,0) as 'May',
   sum(if(month(loaddate) = 6 , drvalue ,0) as 'June'
from tblloadschedule
where cancelloadflag = 'N' and drname like 'sish%'
group by drname

不使用IF进行编辑：

select drname,
   sum((month(loaddate) = 1 ) * drvalue) as 'Jan',
   sum((month(loaddate) = 2 ) * drvalue) as 'Feb',
   sum((month(loaddate) = 3 ) * drvalue) as 'March',
   sum((month(loaddate) = 4 ) * drvalue) as 'April',
   sum((month(loaddate) = 5 ) * drvalue) as 'May',
   sum((month(loaddate) = 6 ) * drvalue) as 'June'
from tblloadschedule
where cancelloadflag = 'N' and drname like 'sish%'
group by drname