字段名 | 函义 | 数据类型 | 是否为空 | 备注 |
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