想了解我的工作情况吗,看下面的,就是我的工作任务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
)
(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,
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,
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
)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货