优化MySchool 08客房系列

use hotel
select * from GuestRecord  --客户记录表
select * from dbo.ResideState  --结账状态(是否结账)
select * from dbo.Room  --房间信息
select * from RoomState  --放间状态
select * from RoomType --房间类型

--分析表结构

--阶段1：指导--统计某类型客房的入住客人人数
--分析：根据用户传入的      [客房类型名称]   ，获取该类型客房的         [客人总数  ]
alter procedure  usp_GetTotalNum
 @Typename nvarchar(32),
 @GuestNum int output  --输出参数
as
 select @GuestNum=COUNT(*) from  GuestRecord
 where RoomID in
 (
   select RoomID from  Room 
   where RoomTypeID in
   (
     select TypeID from RoomType
     where TypeName=@Typename
   )
 )


--调用存储过程：
declare @num int
exec usp_GetTotalNum 'Single',@num output
print 'total：'+convert(nvarchar(20),@num)
--标准间
--单人间
--三人间
--总统套房
--长包房
--豪华标准间
--单人套房
--双人套房
select * from guestrecord
--阶段2练习 --根据房间号查询客房信息
--通过房间号查询客房的相关信息
--如果房间号为-1，表示查询所有客房信息，包括客房ID，床位数，
--客房状态ID,客房类型和房价。
create procedure usp_RoomInfo
 @roomnum int
as 
if(@roomnum=-1)
 begin
   --查询所有客房信息
   select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
	from room,roomtype
	where room.roomtypeid=Roomtype.TypeID
 end

else
begin
	select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
	from room,roomtype
	where room.roomtypeid=Roomtype.TypeID
	and Room.RoomID=@roomnum
end
--调用
exec usp_RoomInfo 1008

--阶段3：删除某种客房类型记录
--需求说明：根据客房类型删除客房类型记录，
--如果操作成功(客房信息表Room没有
--对应记录才可以删除)，返回删除的记录数，否则返回-1
select * from roomtype
select * from room

create procedure  usp_DelListByType
  @typeName nvarchar(20)
as
 if not exists
 (
   select RoomTypeId from room
   where roomtypeid in
   (
     select typeid
     from roomtype
     where typename=@typename
   )
 )
 begin
	delete from roomtype
	where typeName=@typename
    return @@rowcount
end
else  ---房间表中有传入的房间类型记录
return -1

--调用
declare @result int
exec @result=usp_DelListByType '标准间'
if(@result>0)
print '删除了'+convert(nvarchar(20),@result)+'条记录'
else
print @result

--阶段4：练习--插入入住客人记录
 --需求说明：使用存储过程将入住客人信息插入客人信息表中，
 --检查身份证号必须是18个字符组成
 --押金的默认值为1000元
 --将客人入住房间的当前状态设置为"入住"的状态编号
 --如果客人记录插入成功，输出客人流水号。
 create Proc usp_insertGuestRecord
  @identityID nvarchar(20),
  @guestName nvarchar(20),
  @roomID int,
  @resideID int,
  @resideDate datetime,
  @leaveDate datetime,
  @Deposit decimal(18,2)=1000,
  @TotalMoney decimal(18,2),
  @guestID int output
as
 --检查身份证号
  if(len(@identityID)!=18)
  begin
    print '身份证号只能是18位'
    return
  end
  --押金的默认值为1000元(在参数的时候已经设置)
  --插入记录
  begin tran
   declare @ErrorSum int --定义保存错误总和的变量
   insert into GuestRecord
   values(@identityID,@guestName,@roomID,@resideID,
   @resideDate,@leaveDate,@Deposit,@TotalMoney)
   set @ErrorSum=@ErrorSum+@@Error  
   
   --将客人入住房间的当前状态设置为"入住"的状态编号
   declare @roomStateID int
   select @roomStateID=RoomStateID from roomState
   where RoomStateName='已入住'
   
   
   
   update room set RoomStateID=@roomStateID
   where RoomID=@roomID
   set @ErrorSum=@ErrorSum+@@Error
   if(@ErrorSum>0) --insert或者是update这两个过程中至少有一个出现了错误！！！
   begin
     rollback tran
   end
   else
   begin
   commit tran
   set @guestID=@@Identity
   end
   
 --调用
 declare @guestID int
 exec usp_insertGuestRecord 

'410523198806267523','李小龙',1008,1,'2013-3-11','2013-3-15',500,500,@guestID output
if(@guestID>0)
begin
  print '操作成功，客户编号是'+convert(nvarchar(20),@guestID)
end
else
 print '操作失败'
 
 use Myschool
 
 --分组总结
 use MySchool
 select * from result
 order by StudentNo,StudentResult desc
 
 
  
select StudentNo,max(StudentResult)
from Result
group by StudentNo