pro_roomlive_status.sql

来自「一套宾馆管理系统」· SQL 代码 · 共 21 行

create procedure RoomLive_Status
as 
declare @xx table
(room_id int,
 room_no varchar(10),
 flooer_id smallint,
 bed_num smallint,
 live_no int )
insert into @xx 
select room_id,room_no,flooer_id,bed_num,live_no= 
case t1.live_no
when null then 0
else live_no
end 
from roomlist B1 left outer join 
(select room_id temp_id,count(*) Live_no from customerlist
where bedsta_id=2 
group by room_id) t1
on b1.room_id=t1.temp_id 
update @xx set live_no=0 where live_no is null
select * from @xx