表44

select t0.roomtypeid,t0.name,t0.unitprice,
  isnull(t1.area1,0) area1,
  isnull(t1.yjk1,0) yjk1,
  isnull(t2.area2,0) area2,
  isnull(t2.yjk2,0) yjk2,
  isnull(t3.area3,0) area3,
  isnull(t3.jk,0) jk,
  isnull(t4.area4,0) area4,
  isnull(t4.yjk4,0) yjk4,
  isnull(t5.area5,0) area5,
  isnull(t5.yjk5,0) yjk5
from
 (select r.RoomTypeId,r.Name,c.Unitprice
 from RoomType r,chargemode c
 where r.chargemodeid = c.chargemodeId
 union
 select 'a','新楼',0
 ) t0

 left outer join
 (select roomtypeid,sum(area) area1,sum(yjk) yjk1
 from owereportmx
 where yearnum = '2013-2014'
 group by roomtypeid
 
 union
 select 'a',sum(mx.area),sum(mx.yjk)
 from owereportmx mx,owner o
 where mx.ownerid = o.ownerid and mx.yearnum = '2013-2014'
 and o.movetime >= (left(mx.yearnum,4) + '-01-01')
 ) t1
 on t0.roomtypeid = t1.roomtypeid

 left outer join
 (select roomtypeid,sum(area) area2,sum(yjk) yjk2
 from owereportmx
 where yearnum = '2013-2014' and comein = '0'
 group by roomtypeid
 
 union
 select 'a',sum(mx.area),sum(mx.yjk)
 from owereportmx mx,owner o
 where mx.ownerid = o.ownerid and mx.yearnum = '2013-2014' and mx.comein = '0'
  and o.movetime >= (left(mx.yearnum,4) + '-01-01')
 ) t2
 on t0.roomtypeid = t2.roomtypeid

 left outer join
 (select roomtypeid,sum(area) area3,sum(jk) jk
 from owereportmx
 where yearnum = '2013-2014' and jk > 0
 group by roomtypeid
 
 union
 select 'a',sum(mx.area),sum(mx.jk)
 from owereportmx mx,owner o
 where mx.ownerid = o.ownerid and mx.yearnum = '2013-2014' and mx.jk > 0
  and o.movetime >= (left(mx.yearnum,4) + '-01-01')
 ) t3
 on t0.roomtypeid = t3.roomtypeid

 left outer join
 (select roomtypeid,sum(area) area4,sum(yjk) yjk4
 from owereportmx
 where yearnum = '2013-2014' and jk = 0 and comein = '0'
 group by roomtypeid
 
 union
 select 'a',sum(mx.area),sum(mx.yjk)
 from owereportmx mx,owner o
 where mx.ownerid = o.ownerid and mx.yearnum = '2013-2014' and mx.jk = 0 and mx.comein = '0'
  and o.movetime >= (left(mx.yearnum,4) + '-01-01')
 ) t4
 on t0.roomtypeid = t4.roomtypeid

 left outer join
 (select roomtypeid,sum(area) area5,sum(yjk) yjk5
 from owereportmx
 where yearnum = '2013-2014' and jk = 0 and comein <> '0'
 group by roomtypeid
 
 union
 select 'a',sum(mx.area),sum(mx.yjk)
 from owereportmx mx,owner o
 where mx.ownerid = o.ownerid and mx.yearnum = '2013-2014' and mx.jk = 0 and mx.comein <> '0'
  and o.movetime >= (left(mx.yearnum,4) + '-01-01')
 ) t5
 on t0.roomtypeid = t5.roomtypeid
order by t0.roomtypeid