在Table1中有PNO,DDate,PNO表示员工工号,DDate表示日期,该表用于记录某一员工某一天上班记录的信息,现在要查从2008-4-1到2008-04-15号内连续上班等于或大于4天的员工
该语句怎样写?thanks!假如table1中的数据为
A0001 2008-04-01 00:00:00.000
A0001 2008-04-02 00:00:00.000
A0001 2008-04-03 00:00:00.000
A0001 2008-04-10 00:00:00.000
A0001 2008-04-11 00:00:00.000
A0001 2008-04-12 00:00:00.000
A0001 2008-04-13 00:00:00.000
A0002 2008-03-31 00:00:00.000
A0002 2008-04-01 00:00:00.000
A0002 2008-04-02 00:00:00.000
A0002 2008-04-03 00:00:00.000
A0002 2008-04-05 00:00:00.000
则只有A0001才符合
该语句怎样写?thanks!假如table1中的数据为
A0001 2008-04-01 00:00:00.000
A0001 2008-04-02 00:00:00.000
A0001 2008-04-03 00:00:00.000
A0001 2008-04-10 00:00:00.000
A0001 2008-04-11 00:00:00.000
A0001 2008-04-12 00:00:00.000
A0001 2008-04-13 00:00:00.000
A0002 2008-03-31 00:00:00.000
A0002 2008-04-01 00:00:00.000
A0002 2008-04-02 00:00:00.000
A0002 2008-04-03 00:00:00.000
A0002 2008-04-05 00:00:00.000
则只有A0001才符合
from tb
group by pno
having count(1)>=4 and datediff(d,min(ddate),max(ddate))>=4
insert @a select '2004-01-01',1,1
union all select '2004-01-02',1,1
union all select '2004-01-03',1,1
union all select '2004-01-04',1,1
union all select '2004-01-05',1,1
union all select '2004-01-06',1,1
union all select '2004-01-07',1,1
union all select '2004-01-08',1,1
union all select '2004-01-09',1,1
union all select '2004-01-01',2,1
union all select '2004-01-02',2,1
union all select '2004-01-03',2,1
union all select '2004-01-04',2,1
union all select '2004-01-05',2,1
union all select '2004-01-06',2,1
union all select '2004-07-08',2,1
union all select '2004-01-09',2,1
union all select '2004-01-10',2,1--1:
select distinct A.id
from @a A,@a B
where A.d between '2004-01-01' and '2004-02-01'
and B.d between '2004-01-01' and '2004-02-01'
and datediff(day,A.d,B.d)=6
and a.id=B.id
and (select count(*) from @a where d between A.d and B.d and id=A.id and flag=1)=7--2:
select distinct A.id from @a A where d between '2004-01-01' and '2004-02-01'
and exists(select 1 from @a B
where datediff(day,A.d,B.d)=6 and A.id=B.id and d between '2004-01-01' and '2004-02-01'
and (select count(*) from @a where d between A.d and B.d and id=A.id and flag=1)=7 )--3:
select distinct A.id from @a A,@a B,@a C
where A.id=B.id and A.id=C.id
and A.d>='2004-01-01' and A.d<='2004-02-01'
and B.d>='2004-01-01' and B.d<='2004-02-01'
and datediff(d,A.d,B.d)=6 and C.d >=A.d and C.d<=B.d
and A.flag=1 and B.flag=1 and C.flag=1
group by A.id,A.d
having(count(*)>=7)
--4
select distinct A.id from @a A,@a B,
(select 1 as N
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 0
) C
where A.id=B.id
and A.d>='2004-01-01' and A.d<='2004-02-01'
and B.d>='2004-01-01' and B.d<='2004-02-01'
and B.d=dateadd(d,N,A.d)
and A.flag=1 and B.flag=1
group by A.id,A.d
having(count(*)>=7)如何用SQL查询一个月内连续出勤超过7天的人员考勤记录.sql
from tb
group by pno
having count(1)>=4 and datediff(d,min(ddate),max(ddate))>=4
RETURNS int
AS
begin
declare @ii int
set @ii=0
while not exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
set @ii=@ii+1
set @date=dateadd('d',1,@date)
if (@date<>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
ENDselect * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
create table table1(pno varchar(5),DDate datetime)
insert into table1 select 'A0001', '2008-04-01 00:00:00.000'
insert into table1 select 'A0001', '2008-04-02 00:00:00.000'
insert into table1 select 'A0001', '2008-04-03 00:00:00.000'
insert into table1 select 'A0001', '2008-04-10 00:00:00.000'
insert into table1 select 'A0001', '2008-04-11 00:00:00.000'
insert into table1 select 'A0001', '2008-04-12 00:00:00.000'
insert into table1 select 'A0001', '2008-04-13 00:00:00.000'
insert into table1 select 'A0002', '2008-03-31 00:00:00.000'
insert into table1 select 'A0002', '2008-04-01 00:00:00.000'
insert into table1 select 'A0002', '2008-04-02 00:00:00.000'
insert into table1 select 'A0002', '2008-04-03 00:00:00.000'
insert into table1 select 'A0002', '2008-04-05 00:00:00.000' select a.pno
from table1 a
inner join table1 b on a.pno=b.pno and datediff(day,a.DDate,b.DDate)=1
inner join table1 c on b.pno=c.pno and datediff(day,b.DDate,c.DDate)=1
inner join table1 d on c.pno=d.pno and datediff(day,c.DDate,d.DDate)=1
where a.DDate between '2008-04-01 00:00:00.000' and '2008-04-15 00:00:00.000'
/*
------
A0001
*/
(PnO VARCHAR(20),
dDate datetime)insert into a
select 'A0001', '2008-04-01 00:00:00.000' union all
select 'A0001', '2008-04-02 00:00:00.000' union all
select 'A0001', '2008-04-03 00:00:00.000' union all
select 'A0001', '2008-04-10 00:00:00.000' union all
select 'A0001', '2008-04-11 00:00:00.000' union all
select 'A0001', '2008-04-12 00:00:00.000' union all
select 'A0001', '2008-04-13 00:00:00.000' union all
select 'A0002', '2008-03-31 00:00:00.000' union all
select 'A0002', '2008-04-01 00:00:00.000' union all
select 'A0002', '2008-04-02 00:00:00.000' union all
select 'A0002', '2008-04-03 00:00:00.000' union all
select 'A0002', '2008-04-05 00:00:00.000' select distinct pNo from a
where
exists(
select count(1) from a t0
where (a.pno=t0.pno and (t0.ddate <= dateadd(d,3,a.ddate) and t0.ddate>=a.ddate) and
(t0.ddate between '2008-4-1' and '2008-4-15') ) group by t0.pno having count(1)>=4 )
drop table a
insert into Table1
select 'A0001','2008-04-01 00:00:00.000'
union all
select 'A0001','2008-04-02 00:00:00.000'
union all
select 'A0001','2008-04-03 00:00:00.000'
union all
select 'A0001','2008-04-10 00:00:00.000'
union all
select 'A0001','2008-04-11 00:00:00.000'
union all
select 'A0001','2008-04-12 00:00:00.000'
union all
select 'A0001','2008-04-13 00:00:00.000'
union all
select 'A0002','2008-03-31 00:00:00.000'
union all
select 'A0002','2008-04-01 00:00:00.000'
union all
select 'A0002','2008-04-02 00:00:00.000'
union all
select 'A0002','2008-04-03 00:00:00.000'
union all
select 'A0002','2008-04-05 00:00:00.000' Create FUNCTION 判断时间次数(@PNO nvarchar(50),@date datetime)
RETURNS int
AS
begin
declare @ii int
set @ii=0
while exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
set @ii=@ii+1
set @date=dateadd(d,1,@date)
if (@date <>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
END select * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
insert into Table1
select 'A0001','2008-04-01 00:00:00.000'
union all
select 'A0001','2008-04-02 00:00:00.000'
union all
select 'A0001','2008-04-03 00:00:00.000'
union all
select 'A0001','2008-04-10 00:00:00.000'
union all
select 'A0001','2008-04-11 00:00:00.000'
union all
select 'A0001','2008-04-12 00:00:00.000'
union all
select 'A0001','2008-04-13 00:00:00.000'
union all
select 'A0002','2008-03-31 00:00:00.000'
union all
select 'A0002','2008-04-01 00:00:00.000'
union all
select 'A0002','2008-04-02 00:00:00.000'
union all
select 'A0002','2008-04-03 00:00:00.000'
union all
select 'A0002','2008-04-05 00:00:00.000' Create FUNCTION 判断时间次数(@PNO nvarchar(50),@date datetime)
RETURNS int
AS
begin
declare @ii int
set @ii=0
while exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
if (@ii<4)
set @ii=@ii+1
set @date=dateadd(d,1,@date)
if (@ii<4 and @date <>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
END select * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>4
函数里面加了个判断4次的
insert into Table1
select 'A0001','2008-04-01 00:00:00.000'
union all
select 'A0001','2008-04-02 00:00:00.000'
union all
select 'A0001','2008-04-03 00:00:00.000'
union all
select 'A0001','2008-04-10 00:00:00.000'
union all
select 'A0001','2008-04-11 00:00:00.000'
union all
select 'A0001','2008-04-12 00:00:00.000'
union all
select 'A0001','2008-04-13 00:00:00.000'
union all
select 'A0002','2008-03-31 00:00:00.000'
union all
select 'A0002','2008-04-01 00:00:00.000'
union all
select 'A0002','2008-04-02 00:00:00.000'
union all
select 'A0002','2008-04-03 00:00:00.000'
union all
select 'A0002','2008-04-05 00:00:00.000' Create FUNCTION 判断时间次数(@PNO nvarchar(50),@date datetime)
RETURNS int
AS
begin
declare @ii int
set @ii=0
while exists(select * from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date)
begin
if (@ii<4)
set @ii=@ii+1
set @date=dateadd(d,1,@date)
if (@ii<4 and @date <>(select top 1 convert(nvarchar(10),DDate,120) from Table1 where PNO=@PNO and convert(nvarchar(10),DDate,120)>=@date order by DDate asc))
set @ii=0
end
if (@ii is null)
set @ii=0
RETURN @ii
END select * from (
select PNO,dbo.判断时间次数(PNO,convert(nvarchar(10),min(DDate),120)) as 连续次数 from Table1
group by PNO) as a
where 连续次数>=4
create table tb(pno varchar(5),DDate datetime)
insert into tb select 'A0001', '2008-04-01 00:00:00.000'
insert into tb select 'A0001', '2008-04-02 00:00:00.000'
insert into tb select 'A0001', '2008-04-03 00:00:00.000'
insert into tb select 'A0001', '2008-04-10 00:00:00.000'
insert into tb select 'A0001', '2008-04-11 00:00:00.000'
insert into tb select 'A0001', '2008-04-12 00:00:00.000'
insert into tb select 'A0001', '2008-04-13 00:00:00.000'
insert into tb select 'A0002', '2008-03-31 00:00:00.000'
insert into tb select 'A0002', '2008-04-01 00:00:00.000'
insert into tb select 'A0002', '2008-04-02 00:00:00.000'
insert into tb select 'A0002', '2008-04-03 00:00:00.000'
insert into tb select 'A0002', '2008-04-05 00:00:00.000' Create Function f_date(@pno varchar(20),@date datetime)
returns int
begindeclare @n int
set @n=0if exists(select 1 from tb where pno=@Pno and ddate=@date)
begin
while @n<4
begin
if exists(select 1 from tb where pno=@pno and ddate=dateadd(day,@n,@date))
set @n=@n+1else
return @nendend
return @nendselect pno from tb
where ddate between '2008-4-1' and '2008-4-15'
and dbo.f_date(pno,ddate)>=4/*
pno
-----
A0001(1 行受影响)*/