字段名 函义 数据类型 是否为空 备注 CliId 顾客编号 int N PK 自动加1 CliName 顾客姓名 varchar(20) N CliSex 性别 char(10) N CliCertificate 证件号 varchar(50) N 例:身份证号 CliPhone 电话 varchar(50) N 11位 CliAddress 联系地址 varchar(50) Y CliCoding 邮政编码 Int Y CliBrithday 生日 datatime Y CliRemark 备注 varchar(50) Y
属性 函义 数据类型 是否为空 备注 CdId 编号 int N PK 自动加1 CdNumber 卡号 varchar(20) N UQ 唯一 CdPwd 密码 varchar(20) N Md5加密 CdBalance 余额 float N 默认值为0 CgrId 卡级别id int N FK CardGrade EmpId 办理人ID int N FK Employee CliId 顾客编号 Int N FK Clientinfo CdTimeBegin 开卡时间 datetime N 默认getdate() CdTimeEnd 终止时间 datetime N 晚于CdTimeBegin CdRemark 备注 varchar(50) Y CdIntegral 卡积分 Int Y 默认为0
属性 函义 数据类型 是否为空 备注 BusID 交易信息ID int N PK 自动加1 BusNumber 消费单据号 varchar(50) N BusDate 交易日期 datetime N 默认getdate() BusFloat 应付金额 float N EmpId 操作员ID int N FK employee CliId 顾客id int Y FK Clientinfo BusBankCard 银行卡 float N 默认为0 BusCash 现金 float N 默认为0 Bnfree 免单 float Y Default 0 BusState 状态 varchar(20) N 已结帐,未结帐
字段名 函义 数据类型 是否为空 备注 ConId 项目消费编号 int N PK 自动加1 SerId 选择消费项目 int N FK ServeItem EmpId 选择服务师 int N FK Employee BusNumber 消费单据号 varchar(50) N ConCount 次数 int N ConFloat 金额 float N ConDate 消费时间 datetime N getdate() ---【一】 select ConsumeItem.ConDate as '日期',count(ConId) as '客户总数', count(Cards.CdID) as '会员人数' from ConsumeItem,Business,cards,Clientinfo where ConsumeItem.BusNumber=Business.BusNumber and Cards.CliId=Business.CliId group by ConsumeItem.ConDate ----【二】创建视图:view_ConDate_BusNumber if exists(select * from sysobjects where name='view_ConDate_BusNumber') drop view view_ConDate_BusNumber go create view view_ConDate_BusNumber as select convert(char(8),Business.BusDate,112) as 'date',ConsumeItem.BusNumber from ConsumeItem ,Business where ConsumeItem.BusNumber=Business.BusNumber group by convert(char(8),Business.BusDate,112),ConsumeItem.BusNumber go select * from view_ConDate_BusNumber ---【三】创建视图:view_BusDate_CliId_BusNumber if exists(select * from sysobjects where name='view_BusDate_CliId_BusNumber') drop view view_BusDate_CliId_BusNumber go create view view_BusDate_CliId_BusNumber as select convert(char(8),Business.BusDate,112) as 'DateTime',Business.CliId,Business.BusNumber from Business group by convert(char(8),Business.BusDate,112),Business.CliId,Business.BusNumber go select * from view_BusDate_CliId_BusNumber ----【四】创建视图:view_BusDate_CliId_BusNumber_CliID if exists(select * from sysobjects where name='view_BusDate_CliId_BusNumber_CliID') drop view view_BusDate_CliId_BusNumber_CliID go create view view_BusDate_CliId_BusNumber_CliID as select convert(char(8),Business.BusDate,112) as 'datetime_view',Business.CliId as 'CliId',Business.BusNumber as 'BusNumber',Cards.CdID as 'CdID' from Business left outer join Cards on Cards.CliId=Business.CliId group by convert(char(8),Business.BusDate,112),Business.CliId,Business.BusNumber,Cards.CdID go select * from view_BusDate_CliId_BusNumber_CliID ----【五】创建视图:view_BusDate_CliId_BusNumber_CliID if exists(select * from sysobjects where name='view_BusDate_CliId_BusNumber_CliID') drop view view_BusDate_CliId_BusNumber_CliID go create view view_BusDate_CliId_BusNumber_CliID as select convert(char(8),Business.BusDate,112) as 'datetime_view',Business.CliId as 'CliId',Business.BusNumber as 'BusNumber',Cards.CdID as 'CdID',Clientinfo.CliSex as 'Sex' from Business left outer join Cards on Cards.CliId=Business.CliId inner join Clientinfo on Clientinfo.CliId=Business.CliId group by convert(char(8),Business.BusDate,112),Business.CliId,Business.BusNumber,Cards.CdID,Clientinfo.CliSex go select * from view_BusDate_CliId_BusNumber_CliID ----【六】由视图查询: if exists(select * from sysobjects where name='view_BusDate_CliId_BusNumber_CliID') drop view view_BusDate_CliId_BusNumber_CliID go create view view_BusDate_CliId_BusNumber_CliID as select convert(char(8),Business.BusDate,112) as 'datetime_view',count(Business.CliId) as 'CliId',count(Business.BusNumber) as 'BusNumber',Cards.CdID as 'CdID',Clientinfo.CliSex as 'Sex' from Business left outer join Cards on Cards.CliId=Business.CliId inner join Clientinfo on Clientinfo.CliId=Business.CliId group by convert(char(8),Business.BusDate,112),Cards.CdID,Clientinfo.CliSex go select * from view_BusDate_CliId_BusNumber_CliID ---查询结果: select datetime_view as '日期',count(cliid) as '顾客总数',count(cdid) as '会员数',(count(cliid)-count(cdid)) as '非会员数','男顾客'=case when sex='男' then count(sex) else 0 end, '女顾客'=case when sex='女' then count(sex) else 0 end from view_BusDate_CliId_BusNumber_CliID dv group by datetime_view,sex ---【七】:最终结果 select convert(char(8),Business.BusDate,112) as '日期',count(Business.CliId) as '顾客总数',count(Cards.CdID) as '会员数',count(Business.CliId)-count(Cards.CdID) as '非会员数', '男顾客'=case when Clientinfo.CliSex='男' then count(Clientinfo.CliSex) else 0 end, '女顾客'=case when Clientinfo.CliSex='女' then count(Clientinfo.CliSex) else 0 end from Business left outer join Cards on Cards.CliId=Business.CliId inner join Clientinfo on Clientinfo.CliId=Business.CliId group by convert(char(8),Business.BusDate,112),Clientinfo.CliSex
报表[SQL语句]
最新推荐文章于 2024-01-27 15:35:34 发布