我的数据库是sql server 2000的
有一张表tab1,如下
badge name workmins netmins term
ihh1111 小王 0 480 2010-07-05
ihh1111 小王 0 480 2010-07-06
ihh1111 小王 480 480 2010-07-07
ihh1111 小王 0 480 2010-07-08
ihh2222 小立 0 480 2010-07-03
ihh2222 小立 0 480 2010-07-04
ihh2222 小立 0 480 2010-07-05
ihh3333 小张 480 480 2010-07-05
ihh3333 小张 480 480 2010-07-06
ihh4444 小力 0 480 2010-07-06
ihh4444 小力 0 480 2010-07-07
ihh4444 小力 0 480 2010-07-08
ihh4444 小力 0 480 2010-07-12
其中workmins是指实际工作时间,netmins指应工作时间,term指上班日期,其中workmins=0,netmins=480表示该员工当天没上班,现在我要找出连续3天(含)以上没上班的人,并统计没上班的天数(total),如上表我要得到:
badge name workmins netmins term total
ihh2222 小立 0 480 2010-07-03 3
ihh2222 小立 0 480 2010-07-04 3
ihh2222 小立 0 480 2010-07-05 3
ihh4444 小力 0 480 2010-07-06 4
ihh4444 小力 0 480 2010-07-07 4
ihh4444 小力 0 480 2010-07-08 4
ihh4444 小力 0 480 2010-07-12 4
有一张表tab1,如下
badge name workmins netmins term
ihh1111 小王 0 480 2010-07-05
ihh1111 小王 0 480 2010-07-06
ihh1111 小王 480 480 2010-07-07
ihh1111 小王 0 480 2010-07-08
ihh2222 小立 0 480 2010-07-03
ihh2222 小立 0 480 2010-07-04
ihh2222 小立 0 480 2010-07-05
ihh3333 小张 480 480 2010-07-05
ihh3333 小张 480 480 2010-07-06
ihh4444 小力 0 480 2010-07-06
ihh4444 小力 0 480 2010-07-07
ihh4444 小力 0 480 2010-07-08
ihh4444 小力 0 480 2010-07-12
其中workmins是指实际工作时间,netmins指应工作时间,term指上班日期,其中workmins=0,netmins=480表示该员工当天没上班,现在我要找出连续3天(含)以上没上班的人,并统计没上班的天数(total),如上表我要得到:
badge name workmins netmins term total
ihh2222 小立 0 480 2010-07-03 3
ihh2222 小立 0 480 2010-07-04 3
ihh2222 小立 0 480 2010-07-05 3
ihh4444 小力 0 480 2010-07-06 4
ihh4444 小力 0 480 2010-07-07 4
ihh4444 小力 0 480 2010-07-08 4
ihh4444 小力 0 480 2010-07-12 4
SELECT * FROM TAB1 WHERE NAME IN (
SELECT NAME
FROM TAB1 T1
WHERE WORKMINS=0 AND NETMINS>0 AND EXISTS(
SELECT 1 FROM TAB1 T2
WHERE T2.WORKMINS=0 AND T2.NETMINS>0 AND
T2.NAME=T1.NAME AND DATEDIFF(DAY,T1.TERM,T2.TERM)=1
) AND EXISTS(
SELECT 1 FROM TAB1 T2
WHERE T2.WORKMINS=0 AND T2.NETMINS>0 AND
T2.NAME=T1.NAME AND DATEDIFF(DAY,T1.TERM,T2.TERM)=2
))
from tab1 a,tab1 b ,tab1 c
where datediff(day,a.term,b.term)=1 and
datediff(day,a.term,c.term)=2
我也先查出复合条件的数据,具体格式自己在修改吧
SELECT * FROM TAB1 WHERE NAME IN (
SELECT NAME
FROM TAB1 T1
WHERE WORKMINS=0 AND NETMINS>0 AND EXISTS(
SELECT 1 FROM TAB1 T2
WHERE T2.WORKMINS=0 AND T2.NETMINS>0 AND
T2.NAME=T1.NAME AND DATEDIFF(DAY,T1.TERM,T2.TERM)=1
) AND EXISTS(
SELECT 1 FROM TAB1 T2
WHERE T2.WORKMINS=0 AND T2.NETMINS>0 AND
T2.NAME=T1.NAME AND DATEDIFF(DAY,T1.TERM,T2.TERM)=2
)) AND WORKMINS=0 AND NETMINS>0
ihh4444 小力 0 480 2010-07-06ihh4444 小力 0 480 2010-07-07
ihh4444 小力 0 480 2010-07-08
ihh4444 小力 0 480 2010-07-12--第一条不算,如果07/08/12连续三个工作日不上班,不算连续3天旷工的话,那就对。
if object_id('tempdb.dbo.#') is not null drop table #
create table #(badge varchar(8), name varchar(8), workmins int, netmins int, term datetime)
insert into #
select 'ihh1111', '小王', 0, 480, '2010-07-05' union all
select 'ihh1111', '小王', 0, 480, '2010-07-06' union all
select 'ihh1111', '小王', 480, 480, '2010-07-07' union all
select 'ihh1111', '小王', 0, 480, '2010-07-08' union all
select 'ihh2222', '小立', 0, 480, '2010-07-03' union all
select 'ihh2222', '小立', 0, 480, '2010-07-04' union all
select 'ihh2222', '小立', 0, 480, '2010-07-05' union all
select 'ihh3333', '小张', 480, 480, '2010-07-05' union all
select 'ihh3333', '小张', 480, 480, '2010-07-06' union all
--select 'ihh4444', '小力', 0, 480, '2010-07-06' union all
select 'ihh4444', '小力', 0, 480, '2010-07-07' union all
select 'ihh4444', '小力', 0, 480, '2010-07-08' union all
select 'ihh4444', '小力', 0, 480, '2010-07-12'--2000比较难处理,#1对旷工的排序,#2对所有记录排序,如果排序差相等的,证明是连续的。select id=identity(int), * into #1 from # where workmins=0 order by badge, term
select id=identity(int), * into #2 from # order by badge, term
select cid=a.id-b.id, a.* into #3 from #1 a join #2 b on a.badge = b.badge and a.term = b.termselect badge,name,workmins,netmins,term from #3 as t
where (select count(1) from #3 where badge=t.badge and cid=t.cid) >= 3/*
badge name workmins netmins term
-------- -------- ----------- ----------- -----------------------
ihh2222 小立 0 480 2010-07-03 00:00:00.000
ihh2222 小立 0 480 2010-07-04 00:00:00.000
ihh2222 小立 0 480 2010-07-05 00:00:00.000
ihh4444 小力 0 480 2010-07-07 00:00:00.000
ihh4444 小力 0 480 2010-07-08 00:00:00.000
ihh4444 小力 0 480 2010-07-12 00:00:00.000
*/drop table #1,#2,#3