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