本帖最后由 baimei2100 于 2017-6-27 09:09 编辑
下面是我的数据集语句,具体要怎么修改?
SELECT YEAR(oos_shippeddate) as 'Year', oos_productalias as 'Item',
sum(case when month(oos_shippeddate)=1 then oos_realqty else 0 end ) as 'Jan',
sum(case when month(oos_shippeddate)=2 then oos_realqty else 0 end ) as 'Feb',
sum(case when month(oos_shippeddate)=3 then oos_realqty else 0 end ) as 'Mar',
sum(case when month(oos_shippeddate)=4 then oos_realqty else 0 end ) as 'Apr',
sum(case when month(oos_shippeddate)=5 then oos_realqty else 0 end ) as 'May',
sum(case when month(oos_shippeddate)=6 then oos_realqty else 0 end ) as 'Jun',
sum(case when month(oos_shippeddate)=7 then oos_realqty else 0 end ) as 'Jul',
sum(case when month(oos_shippeddate)=8 then oos_realqty else 0 end ) as 'Aug',
sum(case when month(oos_shippeddate)=9 then oos_realqty else 0 end ) as 'Sep',
sum(case when month(oos_shippeddate)=10 then oos_realqty else 0 end ) as 'Oct',
sum(case when month(oos_shippeddate)=11 then oos_realqty else 0 end ) as 'Nov',
sum(case when month(oos_shippeddate)=12 then oos_realqty else 0 end ) as 'Dec',
sum(oos_realqty) as 'Total',
(sum(oos_realqty) /(case when sum(case when month(oos_shippeddate)=1 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=2 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=3 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=4 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=5 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=6 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=7 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=8 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=9 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=10 then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=11then oos_realqty else 0 end )>0 then 1 else 0 end +
case when sum(case when month(oos_shippeddate)=12 then oos_realqty else 0 end )>0 then 1 else 0 end)) as MonthlyAverage
FROM Tbl_D_ProduceOrder
WHERE Year (oos_shippeddate)>2014 and Year(oos_shippeddate) like '%'
group by oos_productalias,Year(oos_shippeddate)
order by Year,Item asc |