select productInfo.accessoryId '零件id',
accessoryNumber '零件编号',
DATENAME(MONTH,indate) as '月份',
sum(case datename(day,inDate) when 1 then number else 0 end )as '1天' ,
sum(case datename(day,inDate) when 2 then number else 0 end )as '2天' ,
sum(case datename(day,inDate) when 3 then number else 0 end )as '3天' ,
sum(case datename(day,inDate) when 4 then number else 0 end )as '4天' ,
sum(case datename(day,inDate) when 5 then number else 0 end )as '5天' ,
sum(case datename(day,inDate) when 6 then number else 0 end )as '6天' ,
sum(case datename(day,inDate) when 7 then number else 0 end )as '7天' ,
sum(case datename(day,inDate) when 8 then number else 0 end )as '8天' ,
sum(case datename(day,inDate) when 9 then number else 0 end )as '9天' ,
sum(case datename(day,inDate) when 10 then number else 0 end )as '10天' ,
sum(case datename(day,inDate) when 11 then number else 0 end )as '11天' ,
sum(case datename(day,inDate) when 12 then number else 0 end )as '12天' ,
sum(case datename(day,inDate) when 13 then number else 0 end )as '13天' ,
sum(case datename(day,inDate) when 14 then number else 0 end )as '14天' ,
sum(case datename(day,inDate) when 15 then number else 0 end )as '15天' ,
sum(case datename(day,inDate) when 16 then number else 0 end )as '16天' ,
sum(case datename(day,inDate) when 17 then number else 0 end )as '17天' ,
sum(case datename(day,inDate) when 18 then number else 0 end )as '18天' ,
sum(case datename(day,inDate) when 19 then number else 0 end )as '19天' ,
sum(case datename(day,inDate) when 20 then number else 0 end )as '20天' ,
sum(case datename(day,inDate) when 21 then number else 0 end )as '21天' ,
sum(case datename(day,inDate) when 22 then number else 0 end )as '22天' ,
sum(case datename(day,inDate) when 23 then number else 0 end )as '23天' ,
sum(case datename(day,inDate) when 24 then number else 0 end )as '24天' ,
sum(case datename(day,inDate) when 25 then number else 0 end )as '25天' ,
sum(case datename(day,inDate) when 26 then number else 0 end )as '26天' ,
sum(case datename(day,inDate) when 27 then number else 0 end )as '27天' ,
sum(case datename(day,inDate) when 28 then number else 0 end )as '28天' ,
sum(case datename(day,inDate) when 29 then number else 0 end )as '29天' ,
sum(case datename(day,inDate) when 30 then number else 0 end )as '30天' ,
sum(case datename(day,inDate) when 31 then number else 0 end )as '31天'
from productInfo,Accessory,monthPlan
where productInfo.accessoryId=Accessory.id and monthPlan.ACCESSORYID=productInfo.accessoryId
group by productInfo.accessoryId,accessoryNumber,DATENAME(MONTH,inDate)
结果为:
产品每月每天的产品量
如何将每天的产量该为累加产量,即第二天的产量为第一天和第二天的产量和,第三天为前三天的产量和??
accessoryNumber '零件编号',
DATENAME(MONTH,indate) as '月份',
sum(case datename(day,inDate) when 1 then number else 0 end )as '1天' ,
sum(case datename(day,inDate) when 2 then number else 0 end )as '2天' ,
sum(case datename(day,inDate) when 3 then number else 0 end )as '3天' ,
sum(case datename(day,inDate) when 4 then number else 0 end )as '4天' ,
sum(case datename(day,inDate) when 5 then number else 0 end )as '5天' ,
sum(case datename(day,inDate) when 6 then number else 0 end )as '6天' ,
sum(case datename(day,inDate) when 7 then number else 0 end )as '7天' ,
sum(case datename(day,inDate) when 8 then number else 0 end )as '8天' ,
sum(case datename(day,inDate) when 9 then number else 0 end )as '9天' ,
sum(case datename(day,inDate) when 10 then number else 0 end )as '10天' ,
sum(case datename(day,inDate) when 11 then number else 0 end )as '11天' ,
sum(case datename(day,inDate) when 12 then number else 0 end )as '12天' ,
sum(case datename(day,inDate) when 13 then number else 0 end )as '13天' ,
sum(case datename(day,inDate) when 14 then number else 0 end )as '14天' ,
sum(case datename(day,inDate) when 15 then number else 0 end )as '15天' ,
sum(case datename(day,inDate) when 16 then number else 0 end )as '16天' ,
sum(case datename(day,inDate) when 17 then number else 0 end )as '17天' ,
sum(case datename(day,inDate) when 18 then number else 0 end )as '18天' ,
sum(case datename(day,inDate) when 19 then number else 0 end )as '19天' ,
sum(case datename(day,inDate) when 20 then number else 0 end )as '20天' ,
sum(case datename(day,inDate) when 21 then number else 0 end )as '21天' ,
sum(case datename(day,inDate) when 22 then number else 0 end )as '22天' ,
sum(case datename(day,inDate) when 23 then number else 0 end )as '23天' ,
sum(case datename(day,inDate) when 24 then number else 0 end )as '24天' ,
sum(case datename(day,inDate) when 25 then number else 0 end )as '25天' ,
sum(case datename(day,inDate) when 26 then number else 0 end )as '26天' ,
sum(case datename(day,inDate) when 27 then number else 0 end )as '27天' ,
sum(case datename(day,inDate) when 28 then number else 0 end )as '28天' ,
sum(case datename(day,inDate) when 29 then number else 0 end )as '29天' ,
sum(case datename(day,inDate) when 30 then number else 0 end )as '30天' ,
sum(case datename(day,inDate) when 31 then number else 0 end )as '31天'
from productInfo,Accessory,monthPlan
where productInfo.accessoryId=Accessory.id and monthPlan.ACCESSORYID=productInfo.accessoryId
group by productInfo.accessoryId,accessoryNumber,DATENAME(MONTH,inDate)
结果为:
产品每月每天的产品量
如何将每天的产量该为累加产量,即第二天的产量为第一天和第二天的产量和,第三天为前三天的产量和??
sum(case when datename(day,inDate) between 1 and 2 then number else 0 end )as '2天' ,
sum(case when datename(day,inDate) between 1 and 3 then number else 0 end )as '3天' ,......
sum(case when datename(day,inDate) <= 1 then number else 0 end )as '1天' ,
sum(case when datename(day,inDate) <= 2 then number else 0 end )as '2天' ,
...
case datename(day,inDate) when 1 then number else 0 end )as '1天'
--一般都这么写
case when datename(day,inDate)=1 then number else 0 end )as '1天'
--要取前面几天的总量,把判断条件改了就行了
case when datename(day,inDate) between 1 and 3 then number else 0 end )as '3天'