請教各位高手以下sql語法需加入sum兩欄位相加分別於每個月

yu0901 2022-06-22 11:45:00 ‧ 750 瀏覽

分享至

請教各位高手以下sql語法需加入sum兩欄位(PARTSTOTAL+MAINTAINEXPENSE)相加分別於每個月該如何下?

--新工作表八(維修費+零件費)0621
select t0.MECHANIC_NAME 維修人員, t0.PARTSTOTAL 實際更換零件, t0.MAINTAINEXPENSE 維修工資, sum(t0.m1) as '1月',sum(t0.m2) as '2月',sum(t0.m3) as '3月',sum(t0.m4) as '4月',sum(t0.m5) as '5月'
from 
	(
	SELECT MECHANIC_NAME,PARTSTOTAL,MAINTAINEXPENSE
		  ,year([RECEIVEDATE]) as year 
		  , case when month([RECEIVEDATE])=1 then SUM (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m1--(RECEIVEDATE 收件日期)
		  , case when month([RECEIVEDATE])=2 then SUM (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m2
		  , case when month([RECEIVEDATE])=3 then SUM (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m3
		  , case when month([RECEIVEDATE])=4 then SUM (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m4
		  , case when month([RECEIVEDATE])=5 then SUM (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m5
	  FROM wo 
	) t0
where t0.year=2022
group by t0.MECHANIC_NAME,t0.MAINTAINEXPENSE

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

allenlwh

iT邦高手 1 級 ‧ 2022-06-23 09:21:55

最佳解答

select t0.MECHANIC_NAME 維修人員
--這裡的m1已經是實際更換零件+實際更換零件的加總了
, sum(t0.m1) as '1月',sum(t0.m2) as '2月',sum(t0.m3) as '3月',sum(t0.m4) as '4月',sum(t0.m5) as '5月'
from 
	(
	SELECT MECHANIC_NAME
		  ,year([RECEIVEDATE]) as year 		  
		  , case when month([RECEIVEDATE])=1 then (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m1--按月份計算 實際更換零件+實際更換零件
		  , case when month([RECEIVEDATE])=2 then (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m2
		  , case when month([RECEIVEDATE])=3 then (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m3
		  , case when month([RECEIVEDATE])=4 then (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m4
		  , case when month([RECEIVEDATE])=5 then (MAINTAINEXPENSE + PARTSTOTAL) else 0 end as m5
	  FROM wo 
	) t0
where t0.year=2022
group by t0.MECHANIC_NAME