表:考勤日期 有字段:员工工号,姓名,考勤日期,矿工(小时/每天),是否假日(1为假日,0为正常上班)我现在需要查询连续3天以上(含3天)旷工名单明细,该如何处理?
请看下图,谢谢各位个解答!
请看下图,谢谢各位个解答!
解决方案 »
- 查找一条记录中非空字段的个数
- SQL Server 2005安装后没有management studio 怎么回事
- 现在在SQL里使用delete in无法删除了。为什么?
- 存储过程的语言
- 急急急!!!求高手帮我解决一个SQL server语句的问题,非常感谢。。。
- 文本类型公式计算问题?
- 如何实现类似这样的功能?where x In ('A%','B%')
- 回味: 如何用sql语句实现成绩求平均分、名次 还有其他方法么?
- 关于SQL Distinct默认结果集排序的探讨?
- 算平均分时小数问题?望请教
- sql2005 不同网段客户端连接的问题
- 关于SQL中的"null",以及一个报错的存储过程
sorry,这个怎么上传图片
if object_id('tempdb.dbo.#') is not null drop table #
create table #(emp_id int, name varchar(8), date datetime, absen int, rest int)
insert into #
select 1, '张三', '2010-08-01', null, 1 union all
select 1, '张三', '2010-08-02', 8, 0 union all
select 1, '张三', '2010-08-03', 8, 0 union all
select 1, '张三', '2010-08-04', 8, 0 union all
select 1, '张三', '2010-08-05', null, 0 union all
select 1, '张三', '2010-08-06', 8, 0 union all
select 1, '张三', '2010-08-07', null, 1 union all
select 1, '张三', '2010-08-08', 8, 0 union all
select 1, '张三', '2010-08-09', 8, 0 union all
select 1, '张三', '2010-08-10', null, 0 union all
select 1, '张三', '2010-08-11', 8, 0 union all
select 1, '张三', '2010-08-12', 8, 0 union all
select 1, '张三', '2010-08-13', null, 1;with t1 as
(
select id = row_number()over(partition by emp_id order by date), * from # where rest = 0
),
t2 as
(
select *, id-row_number()over(partition by emp_id order by id)gid from t1 where absen = 8
)
select emp_id, name, date, absen, rest from t2 t where (select count(1) from t2 where emp_id = t2.emp_id and gid = t.gid) >=3/*
emp_id name date absen rest
----------- -------- ----------------------- ----------- -----------
1 张三 2010-08-02 00:00:00.000 8 0
1 张三 2010-08-03 00:00:00.000 8 0
1 张三 2010-08-04 00:00:00.000 8 0
1 张三 2010-08-06 00:00:00.000 8 0
1 张三 2010-08-08 00:00:00.000 8 0
1 张三 2010-08-09 00:00:00.000 8 0
*/
where (select count(1) from t2 where emp_id = t2.emp_id and gid = t.gid) >=3改为:
where (select count(1) from t2 where emp_id = t.emp_id and gid = t.gid) >=3
select '000001','张三','2010-08-01','',1 union all
select '000001','张三','2010-08-02','8',0 union all
select '000001','张三','2010-08-03','8',0 union all
select '000001','张三','2010-08-04','8',0 union all
select '000001','张三','2010-08-05','',0 union all
select '000001','张三','2010-08-06','8',0 union all
select '000001','张三','2010-08-07','',1 union all
select '000001','张三','2010-08-08','8',0 union all
select '000001','张三','2010-08-09','8',0 union all
select '000001','张三','2010-08-10','',0 union all
select '000001','张三','2010-08-11','8',0 union all
select '000001','张三','2010-08-12','8',0 union
select '000001','张三','2010-08-13','',1 union allselect '000002','李四','2010-08-01','',1 union all
select '000002','李四','2010-08-02','8',0 union all
select '000002','李四','2010-08-03','',0 union all
select '000002','李四','2010-08-04','8',0 union all
select '000002','李四','2010-08-05','8',0 union all
select '000002','李四','2010-08-06','8',0 union all
select '000002','李四','2010-08-07','',1 union all
select '000002','李四','2010-08-08','8',0 union all
select '000002','李四','2010-08-09','',0 union all
select '000002','李四','2010-08-10','8',0 union all
select '000002','李四','2010-08-11','8',0 union all
select '000002','李四','2010-08-12','8',0 union all
select '000002','李四','2010-08-13','',1
;with cte1 as
(
select id1 = row_number()over(partition by eid order by date), * from tb where hol = 0
),
cte2 as
(
select id2 = row_number()over(partition by eid order by date), * from tb where kg = 8
)select a.* from cte1 a join cte2 b on a.eid=b.eid and a.date=b.date
and id1-id2 in
(select id1-id2 from cte1 b join cte2 c on b.eid=c.eid and b.date=c.date where c.eid=a.eid group by id1-id2 having count(*)>=3)练下手....
思路:是将有没休息的记录 与 有旷工 的记录进行比较。(都用ROW_NUMBER()进行了排序)将比较的结果值大于等于三的记录找出来即可