怎么从一个表中查询出某个字段连续重复超过X次的记录

如何从一个表中查询出某个字段连续重复超过X次的记录
有两个表
T1 记录每个班次最大连续重复次数
WID RTimes
1 5
2 3
3 3

T2 员工排班表
DT EID WID
2011-1-1 23 1
2011-1-1 24 3
2011-1-1 25 2
2011-1-2 23 1
2011-1-2 24 3
2011-1-2 25 2
2011-1-3 23 2
2011-1-3 24 3
2011-1-3 25 1
2011-1-4 23 1
2011-1-4 24 3
2011-1-4 25 2

如何从T2表中查询出那些人员在某段时间内的排班 连续重复同一班次超过最大允许的次数
例如上面的演示我想查询到的结果是:
EID=24 的人 WID=3 的班次 连续重复4次 超过3次(最大允许数量)  

我想用存储过程解决 

请 高人指点 谢谢





[解决办法]
select eid,rtimes,etimes
from (select eid,wid,count(*) etimes from t2 group by eid,wid) t
join t1 on t.etimes>t1.rtimes and t.wid=t1.wid
[解决办法]

SQL code

select b.EID,a.WID,a.RTimes,b.no from tb1 a,               (select EID,WID,count(1) as no from tb2 group by EID,WID) bwhere a.WID=b.WID and b.no>a.RTimes
[解决办法]
SQL code
if object_id('p1') is not null    drop proc p1gocreate proc p1@eid intas  declare @wid int,@cnt int,@maxcnt int  select @wid=wid from tb2 where eid=@eid   select @cnt=count(*) from tb2 where eid=@eid  select @maxcnt=rtimes  from tb join tb2 on tb.wid=tb2.wid   where tb2.eid=@eid  if exists(select 1 from tb where rtimes>(select count(*) from tb2 where eid=@eid))      print 'eid='+cast(@eid as varchar(10))+'的人wid='+cast(@wid as varchar(10))             +'的班次 连续重复'+cast(@cnt as varchar(10))+'次 超过'+cast(@maxcnt as varchar(10))             +'次(最大允许数量)'执行：exec p1 24eid=24的人wid=3的班次 连续重复4次 超过3次(最大允许数量)
[解决办法]
SQL code
CREATE TABLE #temp1(WIN INT, RTimes INT)INSERT #temp1SELECT 1, 5 UNION ALL SELECT 2, 3 UNION ALL SELECT 3, 3CREATE TABLE #temp2(DT DATETIME, EID INT, WID INT)INSERT #temp2SELECT '2011-1-1', '23', '1' UNION ALLSELECT '2011-1-1', '24', '3' UNION ALLSELECT '2011-1-1', '25', '2' UNION ALLSELECT '2011-1-2', '23', '1' UNION ALLSELECT '2011-1-2', '24', '3' UNION ALLSELECT '2011-1-2', '25', '2' UNION ALLSELECT '2011-1-3', '23', '2' UNION ALLSELECT '2011-1-3', '24', '3' UNION ALLSELECT '2011-1-3', '25', '1' UNION ALLSELECT '2011-1-4', '23', '1' UNION ALLSELECT '2011-1-4', '24', '3' UNION ALLSELECT '2011-1-4', '25', '2'GO--SQL:SELECT * FROM(    SELECT EID, WID, RTimes, 是否连续=COUNT(DISTINCT calcWid), 是否超过最大次数=COUNT(*) FROM    (        SELECT A.EID, A.WID, B.RTimes, calcWid=C.wid FROM         (SELECT EID, WID, DT = MIN(DT) FROM #temp2 GROUP BY EID, WID) A        CROSS APPLY        (SELECT RTimes = RTimes+1 FROM #temp1 WHERE WIN = A.WID) B        CROSS APPLY        (SELECT TOP(B.RTimes) * FROM #temp2 WHERE EID = A.EID AND DT >= A.DT ORDER BY EID, DT) C    ) T    GROUP BY EID, WID, RTimes) T1WHERE 是否超过最大次数 = Rtimes --某个人连续值班的次数是否 > 最大次数    AND 是否连续 = 1 --次数超过时，班次是否连续/*EID         WID         RTimes      是否连续        是否超过最大次数----------- ----------- ----------- ----------- -----------24          3           4           1           4*/
[解决办法]
探讨

wwwwgou (Shawn) 能不能说说你的思路呀

[解决办法]
考虑利用游标将日期连续的记录置入到临时表，再进行查询比较，过程如下：

CREATE PROCEDURE [sp_666]  

AS
BEGIN
set nocount on

declare @DT datetime,@DT_ls datetime --@DT_ls　原值，与@DT进行比较
declare @EID int,@EID_ls int
declare @WID int,@WID_ls int
--增加一个临时表 dt1＝起始日期　dt2＝连续的截止日期
select dt as dt1,eid,wid , dt as dt2 into t3 from t2 where 1=2 
 

declare cursor1 cursor for select DT,EID,WID from T2 order by eid,wid,dt
open cursor1  
fetch next from cursor1 into @DT,@EID,@WID
while (@@fetch_status=0)
begin
--仅EID相同、WID相同且日期相差一天时，修改续的截止日期dt2
if @EID_ls=@EID and @WID_ls=@WID and DATEDIFF(day,@DT_ls,@DT)=1  
update t3 set dt2=@DT where EID=@EID and WID=@WID and dt2=@DT_ls
else
insert into t3 values(@DT,@EID,@WID,@DT)

set @DT_ls=@DT
set @EID_ls=@EID
set @WID_ls=@WID

fetch next from cursor1 into @DT,@EID,@WID
end
close cursor1
deallocate cursor1

select t3.eid,t3.wid,t3.dt1,t3.dt2,DATEDIFF(day,dt1,dt2)+1 as 连续次数  
from t3 join t1 on  t3.wid=t1.wid and (t3.dt2-t3.dt1)>=t1.rtimes

drop table t3

END