求一条统计连续三天（含）以上没上班员工的人数的查询语句，急急急！

Question

本帖最后由 g505149841 于 2010-08-04 09:23:17 编辑

Answer 1

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (badge varchar(7),name varchar(4),workmins int,netmins int,term datetime)
insert into [tb]
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-09'
go
with wsp
as
(select * from [tb] a
where exists(select 1 from tb where badge=a.badge and workmins=0 and netmins=480 and (term=a.term-1 or term=a.term+1))
and workmins=0 and netmins=480
)
select *,total=(select count(1) from wsp where badge=a.badge)
from wsp a where (select count(1) from wsp where badge=a.badge)>=3--结果：
badge   name workmins    netmins     term                     total
------- ---- ----------- ----------- ----------------------- -----------
ihh2222 小立   0           480         2010-07-03 00:00:00.000 3
ihh2222 小立   0           480         2010-07-04 00:00:00.000 3
ihh2222 小立   0           480         2010-07-05 00:00:00.000 3
ihh4444 小力   0           480         2010-07-06 00:00:00.000 4
ihh4444 小力   0           480         2010-07-07 00:00:00.000 4
ihh4444 小力   0           480         2010-07-08 00:00:00.000 4
ihh4444 小力   0           480         2010-07-09 00:00:00.000 4

Answer 2

不用考虑，因为我的数据库表中休息日，它的netmins（应工作时间）字段会赋0，不会是480，所以在判断员工不上班的条件，已经知道不用考虑，我现在就想得到连续3天以上没上班的员工，谢谢chang99!

Answer 3

if object_id('[tb]') is not null drop table [tb]
create table [tb] (badge varchar(7),name nvarchar(4),workmins int,netmins int,term datetime)
insert into [tb]
select 'ihh1111',N'小王',0,480,'2010-07-05' union all
select 'ihh1111',N'小王',0,480,'2010-07-06' union all
select 'ihh1111',N'小王',480,480,'2010-07-07' union all
select 'ihh1111',N'小王',0,480,'2010-07-08' union all
select 'ihh2222',N'小立',0,480,'2010-07-03' union all
select 'ihh2222',N'小立',0,480,'2010-07-04' union all
select 'ihh2222',N'小立',0,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-07' union all
select 'ihh4444',N'小力',0,480,'2010-07-08' union all
select 'ihh4444',N'小力',0,480,'2010-07-09'
;with a as(
select *,row_number() over(partition by badge order by term) rn from tb where netMins-workmins=480),
b as(
select badge,name,workmins,netmins,term,count(*) over(partition by badge,dateadd(day,-rn,term)) total from a)
select * from b where total>=3

Answer 4

if object_id('[tb]') is not null 
 drop table [tb];
go
create table [tb] 
(badge varchar(7),name nvarchar(4),workmins int,netmins int,term datetime);
go
insert into [tb]
select 'ihh1111',N'小王',0,480,'2010-07-05' union all
select 'ihh1111',N'小王',0,480,'2010-07-06' union all
select 'ihh1111',N'小王',480,480,'2010-07-07' union all
select 'ihh1111',N'小王',0,480,'2010-07-08' union all
select 'ihh2222',N'小立',0,480,'2010-07-03' union all
select 'ihh2222',N'小立',0,480,'2010-07-04' union all
select 'ihh2222',N'小立',0,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-07' union all
select 'ihh4444',N'小力',0,480,'2010-07-08' union all
select 'ihh4444',N'小力',0,480,'2010-07-09';
go;with t1 as(
select *,ROW_NUMBER() over (partition by badge order by term) rn from tb where workmins=0
),
t2 as(
select *,count(*) over (partition by badge,dateadd(dd,-rn,term)) cnt from t1
)
select badge,name,workmins,netmins,term,cnt from t2 where cnt>=3;
/*
ihh2222 小立 0 480 2010-07-03 00:00:00.000 3
ihh2222 小立 0 480 2010-07-04 00:00:00.000 3
ihh2222 小立 0 480 2010-07-05 00:00:00.000 3
ihh4444 小力 0 480 2010-07-06 00:00:00.000 4
ihh4444 小力 0 480 2010-07-07 00:00:00.000 4
ihh4444 小力 0 480 2010-07-08 00:00:00.000 4
ihh4444 小力 0 480 2010-07-09 00:00:00.000 4
*/

Answer 5

if object_id('[tb]') is not null 
 drop table [tb];
go
create table [tb] 
(badge varchar(7),name nvarchar(4),workmins int,netmins int,term datetime);
go
insert into [tb]
select 'ihh1111',N'小王',0,480,'2010-07-05' union all
select 'ihh1111',N'小王',0,480,'2010-07-06' union all
select 'ihh1111',N'小王',480,480,'2010-07-07' union all
select 'ihh1111',N'小王',0,480,'2010-07-08' union all
select 'ihh2222',N'小立',0,480,'2010-07-03' union all
select 'ihh2222',N'小立',0,480,'2010-07-04' union all
select 'ihh2222',N'小立',0,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-05' union all
select 'ihh3333',N'小张',480,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-06' union all
select 'ihh4444',N'小力',0,480,'2010-07-07' union all
select 'ihh4444',N'小力',0,480,'2010-07-08' union all
select 'ihh4444',N'小力',0,480,'2010-07-09';
go
Select s=identity(int,1,1),* into #1 from tb  where workmins=0 and netmins=480
select badge,name,workmins,netmins,term from #1  
where name in (select name from #1 group by name, dateadd(dd,-s,term) having(count(*))>=3)
drop table #1
/*
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-06 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-09 00:00:00.000(7 行受影响)
*/

Answer 6

--利用函数解决，看起来有点笨拙，不像用CTE那么简练
if object_id('tbDate') is not null drop table tbDate
create table tbDate (badge varchar(7),name varchar(4),workmins int,netmins int,term datetime)
insert into tbDate
select 'ihh4444','小力',0,480,'2010-07-09'
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-09'create function getContinuumTime(@name varchar(20),@term varchar(10))
returns varchar(200)
as
begin
declare @i int
declare @j int
declare @k int
declare @ss varchar(200)
set @ss=''
set @i=0
set @j=0
select @i=count(*) from tbDate where name=@name and workmins=0 and term>=@term
if @i<3
return ''
while(@j<@i)
begin
select @k=count(*) from tbDate where name=@name and workmins=0 and term=@term
if @k=1
begin
set @j=@j+1
set @ss=@ss+@term+','
            --set @term=cast(dateadd(day,1,@term) as varchar(10))
            set @term=convert(varchar(10),dateadd(day,1,@term),21)
end
       if @j<3 and @k=0
         return ''
end
return @ss
endselect tt1.*,count(*) over(partition by tt1.badge)
from
(select distinct tbDate.*
from tbDate,(select distinct badge,dbo.getContinuumTime(name,convert(varchar(10),term,21)) as t  from tbDate) tt
where tbDate.badge=tt.badge and charindex(convert(varchar(10),tbDate.term,21),tt.t)>0) tt1

Answer 7

楼上的各位高手，忘了说一句，我的数据库还是用sql server 2000的所有没有CTE啊，怎么办，能不能用嵌套的子查询完成啊，而且数据比较多，帮忙给一条效率高点的语句，谢谢啦！

Answer 8

实在是不好意思，我的自己电脑上是用sql server 2005 能测试的，刚我拿到公司的服务器上运行时，报错了，才知道那服务器上的数据库是2000的，不能运行cte,请大家见谅啊，在帮帮忙啊，解决后，我再加分，谢谢拉！

Answer 9

解决在2000上无法用 OVER（）的问题
select distinct tbDate.* into #temp 
from tbDate,(select distinct badge,dbo.getContinuumTime(name,convert(varchar(10),term,21)) as t  from tbDate) tt
where tbDate.badge=tt.badge and charindex(convert(varchar(10),tbDate.term,21),tt.t)>0select t1.*,t2.num
from #temp t1 inner join
(select badge,count(*) as num
from #temp
group by badge) t2
on t1.badge=t2.badge

Answer 10

高手呢，帮忙解决下啊，最好不用存储过程啊，因为数据库不是我的，我只有查询的权限，谢谢了，数据库是SQL SERVER2000的啊

Answer 11

有没有高手知道在sql server 2000下实现上面的查询啊！谢谢了，解决后一定加分！