再求问:最简洁的连续上班人员统计查询SQL语句,请你帮手?
日考勤结果表Attend的结构如下：
Id 自动编号 ----序
EM_ID 文本(6) ----工号
AttendDate 日期/时间 ----出勤日期
WorkTime 单精度 ----工时
-----------------------------------
id em_id AttendDate WorkTime
1 001 2006-01-01 8.0
2 001 2006-01-02 8.0
3 001 2006-01-03 8.0
4 001 2006-01-04 8.0
5 001 2006-01-05 8.0
6 001 2006-01-06 8.0
7 001 2006-01-07 8.0
8 001 2006-01-08 8.0
9 001 2006-01-10 10.0
10 002 2006-01-01 8.0
11 002 2006-01-02 8.0
12 002 2006-01-03 8.0
13 002 2006-01-04 8.0
14 002 2006-01-05 8.0
15 002 2006-01-06 8.0
16 003 2006-01-01 8.0
17 003 2006-01-02 8.0
18 003 2006-01-03 8.0
用一条SQL语句求得连续上班超过5天的人员记录即：
Em_ID StartDate EndDate
----- ----------- -----------
001 2006-01-01 2006-01-08
002 2006-01-01 2006-01-06
[解决办法]
declare @a table(Id int,EM_ID varchar(6),AttendDate smalldatetime,WorkTime numeric(10,0))
-----------------------------------
insert @a select 1 , '001 ', '2006-01-01 ' ,8.0
union all select 2 , '001 ', '2006-01-02 ' ,8.0
union all select 3 , '001 ', '2006-01-03 ' ,8.0
union all select 4 , '001 ', '2006-01-04 ' ,8.0
union all select 5 , '001 ', '2006-01-05 ', 8.0
union all select 6 , '001 ', '2006-01-06 ', 8.0
union all select 7 , '001 ', '2006-01-07 ', 8.0
union all select 8 , '001 ', '2006-01-08 ', 8.0
union all select 9 , '001 ', '2006-01-10 ', 10.0
union all select 10 , '002 ', '2006-01-01 ', 8.0
union all select 11 , '002 ', '2006-01-02 ', 8.0
union all select 12 , '002 ', '2006-01-03 ', 8.0
union all select 13 , '002 ', '2006-01-04 ', 8.0
union all select 14 , '002 ', '2006-01-05 ', 8.0
union all select 15 , '002 ', '2006-01-06 ', 8.0
union all select 16 , '003 ', '2006-01-01 ', 8.0
union all select 17 , '003 ', '2006-01-02 ', 8.0
union all select 18 , '003 ', '2006-01-03 ', 8.0
select em_id,dateadd(day,-1,min(AttendDate)),max(AttendDate) from @a a where exists(select 1 from @a where datediff(day,attenddate,a.attenddate)=1 ) group by em_id having count(em_id)> 3
[解决办法]
楼上的不对，你去掉union all select 4 , '001 ', '2006-01-04 ' ,8.0 看看。要求连续上班的。
[解决办法]
create table tb(Id int,EM_ID varchar(6),AttendDate smalldatetime,WorkTime numeric(10,0))
-----------------------------------
insert tb select 1 , '001 ', '2006-01-01 ' ,8.0
union all select 2 , '001 ', '2006-01-02 ' ,8.0
union all select 3 , '001 ', '2006-01-03 ' ,8.0
union all select 4 , '001 ', '2006-01-04 ' ,8.0
union all select 5 , '001 ', '2006-01-05 ', 8.0
union all select 6 , '001 ', '2006-01-06 ', 8.0
union all select 7 , '001 ', '2006-01-07 ', 8.0
union all select 8 , '001 ', '2006-01-08 ', 8.0
union all select 9 , '001 ', '2006-01-10 ', 10.0
union all select 10 , '002 ', '2006-01-01 ', 8.0
union all select 11 , '002 ', '2006-01-02 ', 8.0
union all select 12 , '002 ', '2006-01-03 ', 8.0
union all select 13 , '002 ', '2006-01-04 ', 8.0
union all select 14 , '002 ', '2006-01-05 ', 8.0
union all select 15 , '002 ', '2006-01-06 ', 8.0
union all select 16 , '003 ', '2006-01-01 ', 8.0
union all select 17 , '003 ', '2006-01-02 ', 8.0
union all select 18 , '003 ', '2006-01-03 ', 8.0
SELECT a.em_id,min(a.attenddate),max(a.enddate) FROM (
SELECT *,
(SELECT attenddate from tb where id=a.id + 1 and em_id=a.em_id ) as enddate
FROM tb a ) a
GROUP BY a.em_id,DATEDIFF(day,a.attenddate,a.enddate)
HAVING count(a.em_Id) > = 5
drop table tb
[解决办法]
declare @t table(Id int,EM_ID varchar(6),AttendDate smalldatetime,WorkTime numeric(10,0))
insert @t select 1 , '001 ', '2006-01-01 ' ,8.0
union all select 2 , '001 ', '2006-01-02 ' ,8.0
union all select 3 , '001 ', '2006-01-03 ' ,8.0
union all select 4 , '001 ', '2006-01-14 ' ,8.0 (注意这里我改成了1月14日)
union all select 5 , '001 ', '2006-01-05 ', 8.0
union all select 6 , '001 ', '2006-01-06 ', 8.0
union all select 7 , '001 ', '2006-01-07 ', 8.0
union all select 8 , '001 ', '2006-01-08 ', 8.0
union all select 9 , '001 ', '2006-01-10 ', 10.0
union all select 10 , '002 ', '2006-01-01 ', 8.0
union all select 11 , '002 ', '2006-01-02 ', 8.0
union all select 12 , '002 ', '2006-01-03 ', 8.0
union all select 13 , '002 ', '2006-01-04 ', 8.0
union all select 14 , '002 ', '2006-01-05 ', 8.0
union all select 15 , '002 ', '2006-01-06 ', 8.0
union all select 16 , '003 ', '2006-01-01 ', 8.0
union all select 17 , '003 ', '2006-01-02 ', 8.0
union all select 18 , '003 ', '2006-01-03 ', 8.0
select distinct EM_ID from @t a where (select count(distinct AttendDate) from @t
where a.EM_ID=EM_ID and AttendDate> =a.AttendDate and AttendDate <=a.AttendDate+4)=5
结果
EM_ID
------
002
[解决办法]
谁说我错了~~换个就行了~~~
select EM_ID,min(attenddate),max(attenddate)
from (select *,(select count(*) 'ii ' from attend b where b.ID <=a.ID)ii from attend a)c
group by EM_ID, DATEADD(day,-c.ii,attenddate) having count(*)> =5
order by em_id
[解决办法]
再给另外一种写法会简单一点
select EM_ID,begindate,enddate from
(select EM_ID,AttendDate as begindate,(select top 1 AttendDate from @t b
where not exists(select 1 from @t where b.AttendDate=AttendDate-1 and b.EM_ID=EM_ID)
and AttendDate> =a.AttendDate and a.EM_ID=EM_ID order by AttendDate) as EndDate
from @t a where
not exists(select 1 from @t where a.AttendDate=AttendDate+1 and a.EM_ID=EM_ID)) t
where enddate-begindate> =4
[解决办法]
我又笔误了，漏了3个条件
－－－－－－－－－－－－
我刚想说呢，呵呵。
[解决办法]
--还是换成这样好点
create table Attend( Id int,
EM_ID varchar(6),
AttendDate datetime,
WorkTime varchar(5)
)
insert into attend select 1, '001 ', '2006-01-01 ', 8.0
union all select 2, '001 ', '2006-01-02 ', 8.0
union all select 3, '001 ', '2006-01-03 ', 8.0
union all select 5, '001 ', '2006-01-05 ', 8.0
union all select 6, '001 ', '2006-01-06 ', 8.0
union all select 7, '001 ', '2006-01-07 ', 8.0
union all select 8, '001 ', '2006-01-08 ', 8.0
union all select 9, '001 ', '2006-01-10 ', 10.0
union all select 10, '002 ', '2006-01-01 ', 8.0
union all select 11, '002 ', '2006-01-02 ', 8.0
union all select 12, '002 ', '2006-01-03 ', 8.0
union all select 13, '002 ', '2006-01-04 ', 8.0
union all select 14, '002 ', '2006-01-05 ', 8.0
union all select 15, '002 ', '2006-01-06 ', 8.0
union all select 16, '003 ', '2006-01-01 ', 8.0
union all select 17, '003 ', '2006-01-02 ', 8.0
union all select 18, '003 ', '2006-01-03 ', 8.0
select EM_ID,min(attenddate),max(attenddate)
from (select *,
(select count(*) 'ii '
from attend b where b.EM_ID=a.EM_ID and b.AttendDate <=a.AttendDate)ii from attend a)c
group by EM_ID, DATEADD(day,-c.ii,attenddate) having count(*)> =5
[解决办法]
CREATE TABLE Attend
(
Id int,
EM_ID varchar(6),
AttendDate datetime,
WorkTime varchar(5)
)
INSERT INTO Attend
SELECT 1 , '001 ', '2006-01-01 ' ,8.0 UNION ALL
SELECT 2 , '001 ', '2006-01-02 ' ,8.0 UNION ALL
SELECT 3 , '001 ', '2006-01-03 ' ,8.0 UNION ALL
SELECT 4 , '001 ', '2006-01-04 ' ,8.0 UNION ALL
SELECT 5 , '001 ', '2006-01-05 ', 8.0 UNION ALL
SELECT 6 , '001 ', '2006-01-06 ', 8.0 UNION ALL
SELECT 7 , '001 ', '2006-01-07 ', 8.0 UNION ALL
SELECT 8 , '001 ', '2006-01-08 ', 8.0 UNION ALL
SELECT 9 , '001 ', '2006-01-10 ', 10.0 UNION ALL
SELECT 10 , '002 ', '2006-01-01 ', 8.0 UNION ALL
SELECT 11 , '002 ', '2006-01-02 ', 8.0 UNION ALL
SELECT 12 , '002 ', '2006-01-03 ', 8.0 UNION ALL
SELECT 13 , '002 ', '2006-01-04 ', 8.0 UNION ALL
SELECT 14 , '002 ', '2006-01-05 ', 8.0 UNION ALL
SELECT 15 , '002 ', '2006-01-06 ', 8.0 UNION ALL
SELECT 16 , '003 ', '2006-01-01 ', 8.0 UNION ALL
SELECT 17 , '003 ', '2006-01-02 ', 8.0 UNION ALL
SELECT 18 , '003 ', '2006-01-03 ', 8.0
SELECT * FROM Attend A
DROP #t
-----------
CREATE TABLE #t
(
IDEN INT IDENTITY(1,1),
EM_ID VARCHAR(10),
AttendDate datetime,
CNT INT
)
DECLARE tb CURSOR LOCAL
FOR
SELECT EM_ID,AttendDate FROM Attend
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 datetime,@AttendDate_old datetime,@I INT
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@AttendDate_old=DATEADD(DAY,-1,@col2),@I=0
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
BEGIN
IF DATEADD(DAY,1,@AttendDate_old)=@col2
SELECT @I=@I+1,@AttendDate_old=@col2
END
ELSE
BEGIN
INSERT #t(EM_ID,AttendDate,CNT) VALUES(@col1_old,@AttendDate_old,@I)
SELECT @col1_old=@col1,@I=1,@AttendDate_old=@col2
END
FETCH tb INTO @col1,@col2
END
INSERT #t(EM_ID,AttendDate,CNT) VALUES(@col1,@col2,@I)
CLOSE tb
DEALLOCATE tb
SELECT EM_ID,DATEADD(DAY,1-CNT,AttendDate) MINDAY,AttendDate MAXDAY FROM #t WHERE CNT> =5
DROP TABLE #t
-----------------------------------------------
EM_ID MINDAY MAXDAY
---------- ----------------------- -----------------------
001 2006-01-01 00:00:00.000 2006-01-08 00:00:00.000
002 2006-01-01 00:00:00.000 2006-01-06 00:00:00.000
(2 行受影响)
