consumeinfo_view.sql

一个基本的酒店管理系统

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

/******************************************************************************
**		File: 
**		Name:ConsumeInfo_value
**		Desc:  额外消费视图
**
**		This template can be customized:
Nid,房间号
Snumber,数量
Sdatetime,时间
Ctype,类型
Wname,名称
Wprice,价格
Wchar，首字母
Total,消费金额
Aname,操作员
Wunit,单位
**              
**
**		Auth: 
**		Date: 
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/

PRINT 'Creating View ConsumeInfo_value'
GO
CREATE View ConsumeInfo_value
as

select Nid,convert(varchar(20),Snumber)+Wunit as Snumber ,Sdatetime,Ctype,Wname,convert(varchar(20),Wprice)+'￥/'+Wunit as Wprice,Wchar,
convert(varchar(20),(Wprice*Snumber))+'￥'as Total,(select Aname from AdminInfo where Aid=Said)as Aname from 
SuperfluityInfo,wareInfo,ConsumeType 
where SuperfluityInfo.wid=wareInfo.wid and wareInfo.Wcid=ConsumeType.cid


GO


GRANT SELECT ON ConsumeInfo_value TO PUBLIC

GO