roomtotalinfo_view.sql

一个基本的酒店管理系统

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'RoomTotalInfo_View')
	BEGIN
		PRINT 'Dropping View RoomTotalInfo_View'
		DROP  View RoomTotalInfo_View
	END
GO

/******************************************************************************
**		File: 
**		Name: RoomTotalInfo_View
**		Desc: 房间总态
**
**		This template can be customized:
**              
**
**		Auth: 
**		Date: 
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/

PRINT 'Creating View RoomTotalInfo_View'
GO
CREATE View RoomTotalInfo_View
as

select 
roomTotal=(select count(*) from roominfo),--总房间数
kegong=(select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='可供')),--可供数
zhanyong=(select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='占用')),--占用数
yuding=(select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='预定')),
tingyong=(select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='停用')),
qingli=(select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='清理')),
xiaolv=substring(convert(varchar(20),((select count(*) from roominfo where Rstateid=(select stateid from roomstate where stateType='占用'))*0.1/(select count(*) from roominfo)/0.1*100)),1,5)+'%'--入住率


GO


GRANT SELECT ON RoomTotalInfo_View TO PUBLIC

GO