如果你是使用SQL SERVER 2000,那么可以考虑写一个自定义函数来实现。即通过一个自定义函数判断某员工是否满足“连续出勤10天”的条件。然后在SQL语句中调用这个自定义函数就可以了。
以下假定你的表中,workdate 是VARCHAR(10),即形如 '2004-03-01' 一样的字符型字段, worked是整型字段。函数体如下:--此函数判断某员工,在某月,是否连续工作了N天以上 CREATE FUNCTION WorkContinued( @strEmpID varchar(50), @strMonth varchar(7), @intDayCount int) RETURNS int AS begindeclare @WorkDate Varchar(10) declare @WorkDayCount int declare @Result intDECLARE Workdate_Cur CURSOR FOR SELECT workdate FROM EmpWork where empid=@strEmpID and workdate like @strMonth+'%' and worked=1 order by workdateset @Result=0open WorkDate_Cur FETCH NEXT FROM WorkDate_Cur INTO @WorkDate while @@Fetch_status=0 begin Select @WorkDayCount=sum(worked) from EmpWork where empid=@strEmpID and workdate like @strMonth+'%' and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121) if @WorkDayCount>=@intDayCount begin set @Result=1 break end FETCH NEXT FROM WorkDate_Cur INTO @WorkDate endclose WorkDate_Cur deallocate WorkDate_Cur return @Result End创建此函数后,使用select distinct empid from empwork where dbo.WorkContinued(empid ,'2004-03',10)=1我已经测试通过。当然,如果你的workdate是 datetime 型,就需要对函数体进行一些修改了。
再解释一下:函数WorkContinued如果返回1表示此员工满足在此月内连续工作N天,返回0表示不满足。其中的关键SQL语句: Select @WorkDayCount=sum(worked) from EmpWork where empid=@strEmpID and workdate like @strMonth+'%' and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121)是判断从该员工某个工作日开始,其后N天是否连续工作。其原理是,由于Worked字段1表示工作,0表示不工作,那么从某个工作日开始,到其后N天,其Worked字段值SUM后等于N,则说明此员工这N天一直在连续工作;如果小于N,则说明其未连续工作。
select *,0 as Ep into #temp from EmpWork order by empid,workdatedeclare @a int,@b int,@c bit update #temp set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a select distinct empid from #temp where worked=1 and ep=9
select *,0 as Ep into #temp from EmpWork where convert(char(7),workdate,120)='2004-03' order by empid,workdatedeclare @a int,@b int,@c bit update #temp set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a select distinct empid from #temp where worked=1 and ep=9
再改一下: select *,0 as Ep into #temp from EmpWork where convert(char(7),workdate,120)='2004-03' order by empid,workdatedeclare @a int,@b int update #temp set @a=case when @b= empid and worked=1 then @a+1 else 0 end, @b=empid,ep=@a select distinct empid from #temp where worked=1 and ep=9
TO tgc19810107(城市包围农村): 什么叫做“这道题果然满繁的,希望大家用vb继续发言”?如果你的WorkDate字段是CHAR型,那么可以直接用我上面提供的自定义函数; 如果你的WorkDate字段是Datetime型,那么可以直接用yoki(小马哥--鬓微霜,又何妨) 提供的存储过程; 如果你的数据库不是SQL SERVER,那么你看了上述代码之后,也应该可以很容易地写出一个存储过程,或自定义函数来解决。有什么繁的?
查一个人的不出勤天数,最小的一个和第一天比较大于十天不用说了
小于则从这天开始从新判断
一个sql肯定不成了
worked是整型字段。函数体如下:--此函数判断某员工,在某月,是否连续工作了N天以上
CREATE FUNCTION WorkContinued(
@strEmpID varchar(50),
@strMonth varchar(7),
@intDayCount int)
RETURNS int AS
begindeclare @WorkDate Varchar(10)
declare @WorkDayCount int
declare @Result intDECLARE Workdate_Cur CURSOR
FOR SELECT workdate FROM EmpWork
where empid=@strEmpID and workdate like @strMonth+'%' and worked=1
order by workdateset @Result=0open WorkDate_Cur
FETCH NEXT FROM WorkDate_Cur INTO @WorkDate
while @@Fetch_status=0
begin
Select @WorkDayCount=sum(worked) from EmpWork
where empid=@strEmpID and workdate like @strMonth+'%'
and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121) if @WorkDayCount>=@intDayCount
begin
set @Result=1
break
end FETCH NEXT FROM WorkDate_Cur INTO @WorkDate
endclose WorkDate_Cur
deallocate WorkDate_Cur
return @Result
End创建此函数后,使用select distinct empid from empwork
where dbo.WorkContinued(empid ,'2004-03',10)=1我已经测试通过。当然,如果你的workdate是 datetime 型,就需要对函数体进行一些修改了。
Select @WorkDayCount=sum(worked) from EmpWork
where empid=@strEmpID and workdate like @strMonth+'%'
and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121)是判断从该员工某个工作日开始,其后N天是否连续工作。其原理是,由于Worked字段1表示工作,0表示不工作,那么从某个工作日开始,到其后N天,其Worked字段值SUM后等于N,则说明此员工这N天一直在连续工作;如果小于N,则说明其未连续工作。
update #temp set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a
select distinct empid from #temp where worked=1 and ep=9
where convert(char(7),workdate,120)='2004-03'
order by empid,workdatedeclare @a int,@b int,@c bit
update #temp
set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a
select distinct empid from #temp where worked=1 and ep=9
select *,0 as Ep into #temp from EmpWork
where convert(char(7),workdate,120)='2004-03'
order by empid,workdatedeclare @a int,@b int
update #temp set @a=case when @b= empid and worked=1 then @a+1 else 0 end, @b=empid,ep=@a
select distinct empid from #temp where worked=1 and ep=9
什么叫做“这道题果然满繁的,希望大家用vb继续发言”?如果你的WorkDate字段是CHAR型,那么可以直接用我上面提供的自定义函数;
如果你的WorkDate字段是Datetime型,那么可以直接用yoki(小马哥--鬓微霜,又何妨) 提供的存储过程;
如果你的数据库不是SQL SERVER,那么你看了上述代码之后,也应该可以很容易地写出一个存储过程,或自定义函数来解决。有什么繁的?