--创建一个合并处理函数(生成打卡明细时间)
create function f_time(
@员工卡号 nvarchar(6),
@打卡日期 smalldatetime
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+' '+convert(char(5),打卡时间,108)
from 数据表
where 员工卡号=@员工卡号 and 打卡日期=@打卡日期
return(stuff(@re,1,1,''))
end
go--调用函数实现查询
select 员工卡号,打卡日期,打卡明细时间=dbo.f_time(员工卡号,打卡日期)
from 数据表
group by 员工卡号,打卡日期
create function f_time(
@员工卡号 nvarchar(6),
@打卡日期 smalldatetime
)returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+' '+convert(char(5),打卡时间,108)
from 数据表
where 员工卡号=@员工卡号 and 打卡日期=@打卡日期
return(stuff(@re,1,1,''))
end
go--调用函数实现查询
select 员工卡号,打卡日期,打卡明细时间=dbo.f_time(员工卡号,打卡日期)
from 数据表
group by 员工卡号,打卡日期
insert test
select
'687623' , '2004-5-26' , '2004-5-26 7:29:00' union
select '687623' , '2004-5-26' , '2004-5-26 11:5:00' union
select '687623' , '2004-5-26' , '2004-5-26 13:31:00' union
select '687623' , '2004-5-26' , '2004-5-26 17:33:00' union
select '687244' , '2004-5-26' , '2004-5-26 7:35:00' union
select '687244' , '2004-5-26' , '2004-5-26 11:1:00' union
select '687244' , ' 2004-5-26', '2004-5-26 13:28:00' union
select '687244' , '2004-5-26' , '2004-5-26 17:24:00'
drop FUNCTION fun_str
CREATE FUNCTION fun_str(@kh varchar(10),@rq smalldatetime )
RETURNS varchar(100)
AS
BEGIN
declare @str varchar(100)
set @str=''
select @str=@str + ' ' +substring(convert(varchar(20),打卡时间,120),11,10)
from test where 员工卡号=@kh and 打卡日期=@rq
return(@str )
END
select 员工卡号,打卡日期,dbo.fun_str(员工卡号,打卡日期) aa
from (select distinct 员工卡号,打卡日期 from test )aaa
select 员工卡号,打卡日期,打卡明细时间=sumstr(datepart(mi,打卡日期)+datepart(hh,打卡日期)) from test group by 员工卡号,打卡日期