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) b
where a.WID=b.WID and b.no>a.RTimes

------解决方案--------------------
SQL code
if object_id('p1') is not null
    drop proc p1
go
create proc p1
@eid int
as
  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 24

eid=24的人wid=3的班次 连续重复4次 超过3次(最大允许数量)

------解决方案--------------------
SQL code
CREATE TABLE #temp1(WIN INT, RTimes INT)
INSERT #temp1
SELECT 1, 5 UNION ALL SELECT 2, 3 UNION ALL SELECT 3, 3
CREATE TABLE #temp2(DT DATETIME, EID INT, WID INT)
INSERT #temp2
SELECT '2011-1-1', '23', '1' UNION ALL
SELECT '2011-1-1', '24', '3' UNION ALL
SELECT '2011-1-1', '25', '2' UNION ALL
SELECT '2011-1-2', '23', '1' UNION ALL
SELECT '2011-1-2', '24', '3' UNION ALL
SELECT '2011-1-2', '25', '2' UNION ALL
SELECT '2011-1-3', '23', '2' UNION ALL
SELECT '2011-1-3', '24', '3' UNION ALL
SELECT '2011-1-3', '25', '1' UNION ALL
SELECT '2011-1-4', '23', '1' UNION ALL
SELECT '2011-1-4', '24', '3' UNION ALL
SELECT '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
) T1
WHERE 是否超过最大次数 = 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