《在线求一个存储过程！！！！！！！！》

Question

--示例(为方便理解,用中文字段)--表定义
create table 员工信息(
卡号 int primary key,
姓名 varchar(10) not null,
性别 char(2),
年龄 int
--其他信息楼主自己加
)create table 刷卡记录(
卡号 int constraint fk_卡号_刷卡记录_员工信息 
foreign key references 员工信息(卡号)
on update cascade on delete cascade,
时间 datetime
--其他信息楼主自己加
)create table 消费定义(
编号 int identity(1,1) primary key,
消费类别 varchar(10) not null default '早餐' 
constraint chk_消费类别_消费定义 check(消费类别 in('早餐','中餐','晚餐')),
开始时间 datetime,
结束时间 datetime,
价格 money
--其他信息楼主自己加
)
go--日报表(用函数就可以了,即可以带参数,使用起来也方便,没必要用表)
create function 消费日报表(
@日期 datetime --要查询的日期
)returns table
as
return(
select a.卡号,a.姓名,a.性别,a.年龄
,早餐消费次数=sum(case c.消费类别 when '早餐' then 1 else 0 end)
,早餐消费金额=sum(case c.消费类别 when '早餐' then c.价格 else 0 end)
,中餐消费次数=sum(case c.消费类别 when '中餐' then 1 else 0 end)
,中餐消费金额=sum(case c.消费类别 when '中餐' then c.价格 else 0 end)
,晚餐消费次数=sum(case c.消费类别 when '晚餐' then 1 else 0 end)
,晚餐消费金额=sum(case c.消费类别 when '晚餐' then c.价格 else 0 end)
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(varchar(10),@日期,120)
between c.开始时间 and c.结束时间
where datediff(day,@日期,时间)=0
group by a.卡号,a.姓名,a.性别,a.年龄
)
go--查询日报表
select * from 消费日报表('2004-1-1')

Answer 1

--另一种设计方法,可以实现消费类别的自由定义--示例(为方便理解,用中文字段)--表定义
create table 员工信息(
卡号 int primary key,
姓名 varchar(10) not null,
性别 char(2),
年龄 int
--其他信息楼主自己加
)create table 刷卡记录(
卡号 int constraint fk_卡号_刷卡记录_员工信息 
foreign key references 员工信息(卡号)
on update cascade on delete cascade,
时间 datetime
--其他信息楼主自己加
)create table 消费定义(
编号 int identity(1,1) primary key,
消费类别 varchar(10) not null, 
-- default '早餐' 
-- constraint chk_消费类别_消费定义 check(消费类别 in('早餐','中餐','晚餐')),
开始时间 datetime,
结束时间 datetime,
价格 money
--其他信息楼主自己加
)
go--日报表(用存储过程,这样的话,可以实现消费类别的自定义)
create proc p_消费日报表
@日期 datetime --要查询的日期
as
declare @s varchar(8000),@dt char(10)
select @s='',@dt=convert(varchar(10),@日期,120)
select @s=@s+',['+消费类别+'次数]=sum(case c.消费类别 when '''
+消费类别+''' then 1 else 0 end),['+消费类别
+'金额]=sum(case c.消费类别 when '''+消费类别
+''' then c.价格 else 0 end)'
from 消费定义
exec('select a.卡号,a.姓名,a.性别,a.年龄'+@s+'
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-'''+@dt+'''
between c.开始时间 and c.结束时间
where datediff(day,'''+@dt+''',时间)=0
group by a.卡号,a.姓名,a.性别,a.年龄
')
go--查询日报表
exec p_消费日报表 '2004-1-1'
go--删除测试
drop table 刷卡记录,员工信息,消费定义
drop proc p_消费日报表

Answer 2

zjcxc(邹建) 
在日报表中要在一个字段显示刷卡记录的时间，比如2004-4-1 张三在2004-4-1 6：00，2004-4-1 11：00，（这是机器纪录的刷卡时间）吃过反，那么在日报表中我要显示用餐时间这个字段是（2004-4-1），然后在用餐时间里这个字段显示（6：00，11：00）那日报表的生成则么半？我的用餐名称是固定不允许修改的：）
具体的用餐次数在同一天，张三的卡可能打过2次早餐，这也是可能的。

Answer 3

1 employeeId int 4 0
0 employeeName char 10 0
0 departmentId int 4 0
0 departmentName char 10 0
0 mealDate datetime 8 0
0 useCardData char 100 0
0 totalFee float 8 0
0 totalNum int 4 0
0 breakfastNum int 4 0
0 lunchNum int 4 0
0 supperNum int 4 0
以上是日报表的属性
月报表类似日报表
其他的表都和你设计的一样，当然还有个部门表

Answer 4

1 employeeId int 4 0
0 employeeName char 10 0
0 departmentId int 4 0
0 departmentName char 10 0
0 mealDate datetime 8 0
0 useCardData char 100 0
0 totalFee float 8 0
0 totalNum int 4 0
0 breakfastNum int 4 0
0 lunchNum int 4 0
0 supperNum int 4 0
以上是日报表的属性，useCardData就是纪录用餐时间（比如6：00，11：00）的我用char类型，mealdatetime是纪录用餐时间的只要年月日几好了（比如2004-4-1）查询的时候接受员工编号或部门（包括全部部们）或员工姓名查询
月报表类似日报表，显示的字段有员工，部门，总餐费，总次数，早餐次数，中餐次数，晚餐次数。查询的时候接受员工编号或部门（包括全部部们）或员工姓名查询
其他的表都和你设计的一样，当然还有个部门表
以下是员工表
3 employeeId int 4 0
0 emplyeeName char 10 0
0 departmentId int 4 0
0 cardId int 4 0
0 [position] char 10 1
0 age int 4 10 sex char 10 1
0 IDCard char 18 1
0 educationReceived char 10 1

Answer 5

刷卡纪录 我填了
1 2004-1-1 7:00:00
2 2004-1-1 11:00:00
1 2004-1-1 11:20:00
消费定义 我填了   
1 早餐 5:30:00 8:00:00 3（自动编号，餐名，开始时间，结束时间，价格）
2 中餐 11:00:00 12:00:00 5
3 晚餐 17:00:00 19:00:00 5
员工表 填
1 maomao  女 22
2 jingjing 男 22用函数查询select * from 消费日报表('2004-1-1')，怎么没数据显示出来？

Answer 6

--日报表的大致处理方法--创建日报表中乃至的时间处理函数
create function f_time(
@卡号 int,
@日期 datetime,
@消费类别 varchar(10)
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+convert(varchar(5),a.时间,108)
from 刷卡记录 a
join 消费定义 b on a.时间-convert(varchar(10),@日期,120)
between b.开始时间 and b.结束时间
where b.消费类别=@消费类别 and datediff(day,@日期,a.时间)=0
return(substring(@re,2,8000))
end
go--日报表(用函数就可以了,即可以带参数,使用起来也方便,没必要用表)
create function 消费日报表(
@日期 datetime, --要查询的日期
@卡号 int, --要查询的员工编号,如果为NULL,则不限制此条件
@部门编号 int, --查查询的部门编号,如果为NULL,则不限制此条件
@员工姓名 varchar(10) --查查询的员工姓名,如果为NULL,则不限制此条件
)returns table
as
return(
select 部门编号,部门名称,卡号,姓名,性别,年龄
,早餐消费次数,早餐消费金额,早餐消费时间=dbo.f_time(卡号,@日期,'早餐')
,中餐消费次数,中餐消费金额,中餐消费时间=dbo.f_time(卡号,@日期,'中餐')
,晚餐消费次数,晚餐消费金额,晚餐消费时间=dbo.f_time(卡号,@日期,'晚餐')
,总消费次数,总消费金额
from(
select bm.部门编号,bm.部门名称,a.卡号,a.姓名,a.性别,a.年龄
,早餐消费次数=sum(case c.消费类别 when '早餐' then 1 else 0 end)
,早餐消费金额=sum(case c.消费类别 when '早餐' then c.价格 else 0 end)
,中餐消费次数=sum(case c.消费类别 when '中餐' then 1 else 0 end)
,中餐消费金额=sum(case c.消费类别 when '中餐' then c.价格 else 0 end)
,晚餐消费次数=sum(case c.消费类别 when '晚餐' then 1 else 0 end)
,晚餐消费金额=sum(case c.消费类别 when '晚餐' then c.价格 else 0 end)
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 部门 bm on a.部门编号=bm.编号
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(varchar(10),@日期,120)
between c.开始时间 and c.结束时间
where datediff(day,@日期,时间)=0
and a.卡号=isnull(@卡号,a.卡号)
and bm.部门编号=isnull(@部门编号,bm.部门编号)
and a.员工姓名=isnull(@员工姓名,a.员工姓名)
group by a.卡号,a.姓名,a.性别,a.年龄
)a)
go--查询日报表
select * from 消费日报表('2004-1-1',null,null,null)

Answer 7

服务器: 消息 8120，级别 16，状态 1，过程 消费日报表，行 10
列 'bm.部门编号' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
服务器: 消息 8120，级别 16，状态 1，过程 消费日报表，行 10
列 'bm.部门名称' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。
有这个错无啊

Answer 8

以上错误解决。谢谢，
刷卡纪录 我填了
1 2004-1-1 7:00:00
2 2004-1-1 11:00:00
1 2004-1-1 11:20:00
消费定义 我填了   
1 早餐 5:30:00 8:00:00 3（自动编号，餐名，开始时间，结束时间，价格）
2 中餐 11:00:00 12:00:00 5
3 晚餐 17:00:00 19:00:00 5
员工表 填
1 maomao  女 22
2 jingjing 男 22用函数查询select * from 消费日报表('2004-1-1'，null,null,null)，怎么没数据显示出来？

Answer 9

刷新卡记录和消费定义中的数据类型是日期型(ddatetime)吗?

Answer 10

是datetime类型啊，但是没显示出来数据

Answer 11

convert(varchar(10),@日期,120)
返回是char 类型，
from 员工信息 a
join 部门 bm on a.部门编号=bm.编号
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(varchar(10),@日期,120)
b.时间-convert(varchar(10),@日期,120 b.时间是datatime则么-？
是不是应为char类型和datetime类型的比较不行，才没法显示数据？

Answer 12

--检查你的表,我的表测试正常--表定义
create table 员工信息(
卡号 int primary key,
姓名 varchar(10) not null,
性别 char(2),
年龄 int
--其他信息楼主自己加
)create table 刷卡记录(
卡号 int constraint fk_卡号_刷卡记录_员工信息 
foreign key references 员工信息(卡号)
on update cascade on delete cascade,
时间 datetime
--其他信息楼主自己加
)create table 消费定义(
编号 int identity(1,1) primary key,
消费类别 varchar(10) not null default '早餐' 
constraint chk_消费类别_消费定义 check(消费类别 in('早餐','中餐','晚餐')),
开始时间 datetime,
结束时间 datetime,
价格 money
--其他信息楼主自己加
)
go--插入测试数据
insert 消费定义 select '早餐','5:30:00','8:00:00',3
union  all     select '中餐','11:00:00','12:00:00',5
union  all     select '晚餐','17:00:00','19:00:00',5insert 员工信息 select 1,'maomao','女',22
union  all     select 2,'jingjing','男',22insert 刷卡记录 select 1,'2004-1-1 7:00:00'
union  all     select 2,'2004-1-1 11:00:00'
union  all     select 1,'2004-1-1 11:20:00'
go
--日报表(用函数就可以了,即可以带参数,使用起来也方便,没必要用表)
create function 消费日报表(
@日期 datetime --要查询的日期
)returns table
as
return(
select a.卡号,a.姓名,a.性别,a.年龄
,早餐消费次数=sum(case c.消费类别 when '早餐' then 1 else 0 end)
,早餐消费金额=sum(case c.消费类别 when '早餐' then c.价格 else 0 end)
,中餐消费次数=sum(case c.消费类别 when '中餐' then 1 else 0 end)
,中餐消费金额=sum(case c.消费类别 when '中餐' then c.价格 else 0 end)
,晚餐消费次数=sum(case c.消费类别 when '晚餐' then 1 else 0 end)
,晚餐消费金额=sum(case c.消费类别 when '晚餐' then c.价格 else 0 end)
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(varchar(10),@日期,120)
between c.开始时间 and c.结束时间
where datediff(day,@日期,时间)=0
group by a.卡号,a.姓名,a.性别,a.年龄
)
go--查询日报表
select * from 消费日报表('2004-1-1')
godrop table 刷卡记录,消费定义,员工信息
drop function 消费日报表/*--测试结果1 maomao 女 22 1 3.0000 1 5.0000 0 .0000 2 8.0000
2 jingjing 男 22 0 .0000 1 5.0000 0 .0000 1 5.0000
--*/

Answer 13

zjcxc(邹建) 能把你见的表和函数给我吗？
如果帖出来比较多，你发我油箱[email protected]

Answer 14

--日报表的大致处理方法--创建日报表中乃至的时间处理函数
create function f_time(
@卡号 int,
@日期 datetime,
@消费类别 varchar(10)
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+convert(varchar(5),a.时间,108)
from 刷卡记录 a
join 消费定义 b on a.时间-convert(varchar(10),@日期,120)
between b.开始时间 and b.结束时间
where b.消费类别=@消费类别 and datediff(day,@日期,a.时间)=0
return(substring(@re,2,8000))
end
go--日报表(用函数就可以了,即可以带参数,使用起来也方便,没必要用表)
create function 消费日报表(
@日期 datetime, --要查询的日期
@卡号 int, --要查询的员工编号,如果为NULL,则不限制此条件
@部门编号 int, --查查询的部门编号,如果为NULL,则不限制此条件
@员工姓名 varchar(10) --查查询的员工姓名,如果为NULL,则不限制此条件
)returns table
as
return(
select 部门编号,部门名称,卡号,姓名,性别,年龄
,早餐消费次数,早餐消费金额,早餐消费时间=dbo.f_time(卡号,@日期,'早餐')
,中餐消费次数,中餐消费金额,中餐消费时间=dbo.f_time(卡号,@日期,'中餐')
,晚餐消费次数,晚餐消费金额,晚餐消费时间=dbo.f_time(卡号,@日期,'晚餐')
,总消费次数,总消费金额
from(
select bm.部门编号,bm.部门名称,a.卡号,a.姓名,a.性别,a.年龄
,早餐消费次数=sum(case c.消费类别 when '早餐' then 1 else 0 end)
,早餐消费金额=sum(case c.消费类别 when '早餐' then c.价格 else 0 end)
,中餐消费次数=sum(case c.消费类别 when '中餐' then 1 else 0 end)
,中餐消费金额=sum(case c.消费类别 when '中餐' then c.价格 else 0 end)
,晚餐消费次数=sum(case c.消费类别 when '晚餐' then 1 else 0 end)
,晚餐消费金额=sum(case c.消费类别 when '晚餐' then c.价格 else 0 end)
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 部门 bm on a.部门编号=bm.编号
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(varchar(10),@日期,120)
between c.开始时间 and c.结束时间
where datediff(day,@日期,时间)=0
and a.卡号=isnull(@卡号,a.卡号)
and bm.部门编号=isnull(@部门编号,bm.部门编号)
and a.员工姓名=isnull(@员工姓名,a.员工姓名)
group by a.卡号,a.姓名,a.性别,a.年龄
)a)
go--查询日报表
select * from 消费日报表('2004-1-1',null,null,null)
这个函数有问题，应为按我门填的数据，jingjing  是没吃过早蚕的，但是显示的时候却把maomao  吃过的早餐时间但做jingjing 的时间显示出来了。

Answer 15

--少写了条件限制
create function f_time(
@卡号 int,
@日期 datetime,
@消费类别 varchar(10)
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+convert(varchar(5),a.时间,108)
from 刷卡记录 a
join 消费定义 b on a.时间-convert(varchar(10),@日期,120)
between b.开始时间 and b.结束时间
where a.卡号=@卡号 and b.消费类别=@消费类别 and datediff(day,@日期,a.时间)=0
return(substring(@re,2,8000))
end
go

Answer 16

--日报表的大致处理方法--创建日报表中乃至的时间处理函数
create function f_time(
@卡号 int,
@日期 datetime,
@消费类别 varchar(10)
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+','+convert(varchar(5),a.时间,108)
from 刷卡记录 a
join 消费定义 b on a.时间-convert(varchar(10),@日期,120)
between b.开始时间 and b.结束时间
where a.卡号=@卡号 and b.消费类别=@消费类别 and datediff(day,@日期,a.时间)=0
return(substring(@re,2,8000))
end
go--日报表(用函数就可以了,即可以带参数,使用起来也方便,没必要用表)
create function 消费日报表(
@日期1 datetime, --要查询的日期
@日期2 datetime, --结束日期
@卡号 int, --要查询的员工编号,如果为NULL,则不限制此条件
@部门编号 int, --查查询的部门编号,如果为NULL,则不限制此条件
@员工姓名 varchar(10) --查查询的员工姓名,如果为NULL,则不限制此条件
)returns table
as
return(
select 日期,部门编号,部门名称,卡号,姓名,性别,年龄
,早餐消费次数,早餐消费金额,早餐消费时间=dbo.f_time(卡号,日期,'早餐')
,中餐消费次数,中餐消费金额,中餐消费时间=dbo.f_time(卡号,日期,'中餐')
,晚餐消费次数,晚餐消费金额,晚餐消费时间=dbo.f_time(卡号,日期,'晚餐')
,总消费次数,总消费金额
from(
select 日期=convert(char(10),b.时间,120)
,bm.部门编号,bm.部门名称,a.卡号,a.姓名,a.性别,a.年龄
,早餐消费次数=sum(case c.消费类别 when '早餐' then 1 else 0 end)
,早餐消费金额=sum(case c.消费类别 when '早餐' then c.价格 else 0 end)
,中餐消费次数=sum(case c.消费类别 when '中餐' then 1 else 0 end)
,中餐消费金额=sum(case c.消费类别 when '中餐' then c.价格 else 0 end)
,晚餐消费次数=sum(case c.消费类别 when '晚餐' then 1 else 0 end)
,晚餐消费金额=sum(case c.消费类别 when '晚餐' then c.价格 else 0 end)
,总消费次数=count(*),总消费金额=sum(c.价格)
from 员工信息 a
join 部门 bm on a.部门编号=bm.编号
join 刷卡记录 b on a.卡号=b.卡号
join 消费定义 c on b.时间-convert(char(10),b.时间,120)
between c.开始时间 and c.结束时间
where datediff(day,@日期1,时间)>=0 and datediff(day,@日期2,时间)<=0
and a.卡号=isnull(@卡号,a.卡号)
and bm.部门编号=isnull(@部门编号,bm.部门编号)
and a.员工姓名=isnull(@员工姓名,a.员工姓名)
group by convert(char(10),b.时间,120),a.卡号,a.姓名,a.性别,a.年龄
)a)
go--查询日报表
select * from 消费日报表('2004-1-1',null,null,null)