公司房态图运行太慢,要优化SQL,于是花了几天时间优化ORACLE的SQL语句,表明比以前确实快多了。 
with qq as 
(select bbb.roomid,max(bbb.innid) innid 
from inninfo bbb  
where  bbb.isleave='N' group by bbb.roomid ) 

Select a.*,
N''||substr(longguestname,1,length(longguestname)-1)  GuestName,
nvl(vvv.www,0)    PayMoney,xxx.ismsg2   RelationInnInfo 
from Room a 
left join
(  
select qq.roomid,ConnStrSum(to_char(ggg.name||','))   longguestname
from qq ,inndetail iid,guestinfo ggg
where qq.innid=iid.innid
and iid.guestid=ggg.guestid
group by qq.roomid
) zzz on a.roomid=zzz.roomid  
left join 
(
select  qq.roomid,
Nvl(Sum(decode( c.flag,'A',(-1)*bb.paymoney,bb.paymoney )),0)  www
from  qq ,
AccountDetail bb,
AccountPara c
where
qq.INNID=bb.INNID 
and bb.CODE=c.CODE 
group by qq.roomid
) vvv on a.roomid=vvv.roomid
left join 
(select  qq.roomid,'InnId='||qq.InnId||'DirtyRoomFlag='||DirtyRoomFlag||'LeaveDate='||to_Char

(LeaveBakDate,'yyyy-mm-dd')||'IsMsg='||decode(yyy.innid,null,'N','Y') 
||'isLease='||isLease||'ActPrice='||Nvl(ActPrice,0)||' HRCode='||HRCode||' InnDay='||InnDay||'

MorningCall='||MorningCall||' MorningCallTime='||To_Char(MorningCallTime,'hh24:mi:ss') ismsg2
from qq,(select distinct innid from GuestMessage) yyy,InnInfo x
where
qq.innid=yyy.innid(+)
and x.innid=qq.innid  ) xxx on a.roomid=xxx.roomid
Order by a.Building,a.Floor,a.RoomCode
////////////////////////////////////////////////////////////////////////////////////////
旧roomview:
//////////////////////////////////////
with qq as 
(select aaa.roomid,max(bbb.innid) innid 
from Room aaa left join  inninfo bbb  
on aaa.roomid=bbb.roomid  and bbb.isleave='N' group by aaa.roomid ) 

Select a.*,
N''||substr(longguestname,1,length(longguestname)-1)  GuestName,
nvl(vvv.www,0)    PayMoney,xxx.ismsg2   RelationInnInfo 
from Room a 
left join
(  
select distinct roomid,ConnStrSum(to_char(min(name||','))) over (PARTITION BY  roomid )  
longguestname
from(
select qqq.*,ggg.name,rownum rowidd
from inninfo iii,inndetail iid,guestinfo ggg,qq qqq
where iii.innid=iid.innid
and iid.guestid=ggg.guestid
and iii.innid=qqq.innid
) vbvc
group by roomid,rowidd
) zzz on a.roomid=zzz.roomid  
left join 
(
select  aa.roomid,
Nvl(Sum(case when c.flag='A' then (-1)*bb.paymoney when c.flag='B' then bb.paymoney end),0)  www
from InnInfo aa
left join AccountDetail bb on aa.INNID=bb.INNID 
left join AccountPara c on bb.CODE=c.CODE 
left join AccountStatus d on bb.STATUSCODE=d.STATUSCODE 
left join Room e on e.RoomId=aa.RoomId,
qq
where e.StatusCode in (1,3)  
and aa.RoomId=qq.roomid
and aa.InnId=qq.innid
group by aa.roomid
) vvv on a.roomid=vvv.roomid
left join 
(select  qq.roomid,'InnId='||x.InnId||'DirtyRoomFlag='||DirtyRoomFlag||'LeaveDate='||to_Char

(LeaveBakDate,'yyyy-mm-dd')||'IsMsg='||decode(yyy.innid,null,'N','Y') 
||'isLease='||isLease||'ActPrice='||Nvl(ActPrice,0)||' HRCode='||HRCode||' InnDay='||InnDay||'

MorningCall='||MorningCall||' MorningCallTime='||To_Char(MorningCallTime,'hh24:mi:ss') ismsg2
from InnInfo x,(select distinct innid from GuestMessage) yyy,
qq 
where x.innid=qq.innid 
and x.innid=yyy.innid(+)  ) xxx on a.roomid=xxx.roomid
Order by a.Building,a.Floor,a.RoomCode
////////////////////////////////////
select * from roomview
minus
select * from roomviewli


////////////////////////
select * from roomviewli
where roomcode  in
(
select roomcode from
(
select * from roomview
minus
select * from roomviewli
) www
)