蛋疼的视图

create view test
as
 select aa.years, aa.months, aa.sales_famount, bb.installation_famount, bb.installation_fallamount, cc.transport_famount, cc.transport_fallamount,
 dd.Steelroll_fallqty, ee.profiles_fallqty, ff.Purchased_fallqty, gg.spraying_fallqty, hh.delivery_fallqty
 from
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum(case a.fcurrencyid when 1000 then b.famount*a.fexchangerate else b.famount end) as sales_famount
 from seorder a left join seorderentry b on a.finterid=b.finterid left join t_item c on b.fitemid=c.fitemid
 where c.fnumber not in ('WC901-2', '02-快递费', 'WC901-1', '02-安装费')
 group by year(a.fdate), month(a.fdate)
 )aa left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum(case fcurrencyid when 1000 then b.famount*a.fexchangerate else b.famount end) as installation_famount, sum(case fcurrencyid when 1000 then b.fallamount*a.fexchangerate else b.fallamount end) as installation_fallamount
 from seorder a left join seorderentry b on a.finterid=b.finterid left join t_item c on b.fitemid=c.fitemid
 where fnumber in ('WC901-1','02-安装费')    --安装费用
 group by year(a.fdate), month(a.fdate)
 )bb on aa.years = bb.years and aa.months = bb.months
 left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum(case fcurrencyid when 1000 then b.famount*a.fexchangerate else b.famount end) as transport_famount/*价税合计*/, sum(case fcurrencyid when 1000 then b.fallamount*a.fexchangerate else b.fallamount end) as transport_fallamount /*无折扣金额*/
 from seorder a left join seorderentry b on a.finterid=b.finterid left join t_item c on b.fitemid=c.fitemid
 where c.fnumber in ('WC901-2','02-快递费')   --运输费
 group by year(a.fdate), month(a.fdate)
 )cc on aa.years = cc.years and aa.months = cc.months
 left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum(b.fqty)/1000 as Steelroll_fallqty
 from icstockbill a left join icstockbillentry b on a.finterid=b.finterid left join t_item c on b.fitemid=c.fitemid
 where substring(c.fnumber,1,1)='j' and substring(a.fbillno,1,4)='sout'
 group by  year(a.fdate), month(a.fdate)
 )dd on aa.years = dd.years and aa.months = dd.months
 left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum(b.fqty)/1000 as profiles_fallqty
 from icstockbill a left join icstockbillentry b on a.finterid=b.finterid left join t_item c on b.fitemid=c.fitemid
 where substring(c.fnumber,1,1)='c' and substring(a.fbillno,1,4)='sout'
 group by  year(a.fdate), month(a.fdate)
 )ee on aa.years = ee.years and aa.months = ee.months
 left join
 (
  select tt.years, tt.months, sum(tt.Purchased_fallqty) as Purchased_fallqty
  from
  (
   select year(a.fdate) as years, month(a.fdate) as months, sum(b.fqty)/1000 as Purchased_fallqty
   from icstockbill a left join icstockbillentry b on a.finterid=b.finterid left join t_ICItemCore c on b.fitemid=c.fitemid left join t_ICItemBase d on c.fitemid=d.fitemid
   where d.ferpclsid=1
   and (substring(fnumber,1,2) not in ('01', '02', '03', '04', 'BM', 'DE', 'pt')
   and substring(fnumber,1,3) not in ('p07', 'p08', 'p11', 'p12', 'p13', 'p14', 'p24', 'p30', 'p33', 'p50', 'p60', 'p61', 'p62', 'p69', 'p70', 'p80', 'p81', 'p82', 'pcb', 'pcc', 'pcd', 'pce', 'pcf', 'pcg')
   and  substring(fnumber,1,1) not in ('j', 'c'))
   and (c.fname not like '%橡胶套%'
   and c.fname not like '%贴纸%'
   and c.fname not like '%阳光板%'
   and c.fname not like '%垫圈%'
   and c.fname not like '%胶块%'
   and c.fname not like '%胶垫%'
   and c.fname not like '%安全网%')
   and substring(a.fbillno,1,4)='sout'
   group by year(a.fdate), month(a.fdate)
   union all
   select year(a.fdate) as years, month(a.fdate) as months, sum(a.fauxqty)/1000 as Purchased_fallqty
   from VWicbill_8  a left join t_icitem b on a.ffullnumber=b.fnumber
   where b.ferpclsid=1
   and (substring(b.fnumber,1,2) not in ('01','02','03','04','BM','DE','pt')
   and substring(b.fnumber,1,3) not in ('p07','p08','p11','p12','p13','p14','p24','p30','p33','p50','p60','p61','p62','p69','p70','p80','p81','p82','pcb','pcc','pcd','pce','pcf','pcg')
   and  substring(b.fnumber,1,1) not in ('j','c'))
   and (b.fname not like '%橡胶套%'
   and b.fname not like '%贴纸%'
   and b.fname not like '%阳光板%'
   and b.fname not like '%垫圈%'
   and b.fname not like '%胶块%'
   and b.fname not like '%胶垫%'
   and b.fname not like '%安全网%')
   group by year(a.fdate), month(a.fdate)
  )tt
  group by tt.years, tt.months
 )ff on aa.years = ff.years and aa.months = ff.months
 left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum((d.flength)*(b.fqty)) as spraying_fallqty
 from icstockbill a left join  icstockbillentry b on a.finterid=b.finterid left join  t_ICItemCore c on b.fitemid=c.fitemid left join  t_ICItemDesign d on b.fitemid=d.fitemid left join  t_ICItemBase e on b.fitemid=e.fitemid
 where  e.ferpclsid in ('2','3') and right(fnumber,1)<>'-' and substring(a.fbillno,1,3) in ('cin','jin') and d.fversion in ('d','s')
 group by  year(a.fdate), month(a.fdate)
 )gg on aa.years = gg.years and aa.months = gg.months
 left join
 (
 select year(a.fdate) as years, month(a.fdate) as months, sum((a.fauxqty)*(c.fgrossweight))/1000 as delivery_fallqty
 from VWicbill_8  a left join t_ICItemCore b on a.ffullnumber=b.fnumber left join t_ICItemDesign c on b.FItemID=c.FItemID 
 group by year(a.fdate), month(a.fdate)
 )hh on aa.years = hh.years and aa.months = hh.months
 order by aa.years, aa.months