有两个表
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 [email protected] select @cnt=count(*) from tb2 where [email protected] select @maxcnt=rtimes from tb join tb2 on tb.wid=tb2.wid where [email protected] if exists(select 1 from tb where rtimes>(select count(*) from tb2 where [email protected])) 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*/
------解决方案--------------------
------解决方案--------------------
考虑利用游标将日期连续的记录置入到临时表，再进行查询比较，过程如下：
CREATE PROCEDURE [sp_666]
AS
BEGIN
set nocount on
declare @DT datetime,@DT_ls datetime [email protected]_ls 原值，[email protected]
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