27,579
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tbTest')
DROP TABLE tbTest
CREATE TABLE tbTest(employee VARCHAR(20), --员工编号
StartTime DATETIME, --休息开始时间,都是按照刻钟为起点,格式为小时00、分钟00 如早上8点为800
RestType INT)--0:表示工作状态,其他表示各类休息状态
SELECT GETDATE()
INSERT INTO tbTest(employee, StartTime, RestType)
SELECT 'emp1001', '2010-12-01 08:00:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:15:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:30:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:45:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:15:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 12:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 12:45:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 13:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 13:45:00', 1
/*
其中的emp1001 2010-12-01 08:15:00 1
表示员工emp1001在2010年12月1号这天8点到8点15分为类型为1的休息
*/
SELECT * FROM tbTest
/*
现在需要统计员工在各个休息类型的休息时长,在统计时时间不能交叉
希望得到的数据样式为
employee RestStartTime RestType RestTimeLen
-------------------- -------------------------------- ----------- -----------
emp1001 2010-12-01 08:00:00 1 45
emp1001 2010-12-01 09:15:00 2 30
emp1001 2010-12-01 09:45:00 3 30
emp1001 2010-12-01 10:15:00 1 30
emp1001 2010-12-01 12:15:00 3 15
emp1001 2010-12-01 12:30:00 1 30
emp1001 2010-12-01 13:00:00 3 30
emp1001 2010-12-01 13:45:00 1 15
*/
/*
* 注意事项:
* 1.需要考虑大量数据,每次一次性处理会在10万以上
* 2.尽可能避免使用循环、触发器等
* 3.可以使用表变量、临时表、函数、存储过程等
*/
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tbTest')
DROP TABLE tbTest
CREATE TABLE tbTest(employee VARCHAR(20), --员工编号
StartTime DATETIME, --休息开始时间,都是按照刻钟为起点,格式为小时00、分钟00 如早上8点为800
RestType INT)--0:表示工作状态,其他表示各类休息状态
INSERT INTO tbTest(employee, StartTime, RestType)
SELECT 'emp1001', '2010-12-01 08:00:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:15:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:30:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:45:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:15:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 12:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 12:45:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 13:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 13:45:00', 1
alter table tbtest add px int
go
declare @n int,@state int
set @n=1
set @state=(select top 1 RestType from tbtest order by StartTime )
update tbtest
set @n=case when RestType=@state then @n else @n+1 end,@state=RestType,px=@n
;with cte as
(
select * -- ,RestTimeLen=
from tbtest t
where not exists(select 1 from tbtest where employee=t.employee and px=t.px and StartTime<t.StartTime)
)
select a.employee,a.StartTime,a.RestType ,
RestTimeLen=isnull(datediff(minute,a.StartTime,b.StartTime),15)
from cte a
left join cte b on a.employee=b.employee and a.px=b.px-1
where a.RestType<>0
employee StartTime RestType RestTimeLen
-------------------- ----------------------- ----------- -----------
emp1001 2010-12-01 08:00:00.000 1 45
emp1001 2010-12-01 09:15:00.000 2 30
emp1001 2010-12-01 09:45:00.000 3 30
emp1001 2010-12-01 10:15:00.000 1 30
emp1001 2010-12-01 12:15:00.000 3 15
emp1001 2010-12-01 12:30:00.000 1 30
emp1001 2010-12-01 13:00:00.000 3 30
emp1001 2010-12-01 13:45:00.000 1 15
(8 行受影响)
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tbTest')
DROP TABLE tbTest
CREATE TABLE tbTest(employee VARCHAR(20), --员工编号
StartTime DATETIME, --休息开始时间,都是按照刻钟为起点,格式为小时00、分钟00 如早上8点为800
RestType INT)--0:表示工作状态,其他表示各类休息状态
INSERT INTO tbTest(employee, StartTime, RestType)
SELECT 'emp1001', '2010-12-01 08:00:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 08:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 09:15:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:30:00', 2 UNION ALL
SELECT 'emp1001', '2010-12-01 09:45:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 10:15:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 10:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:15:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 11:45:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:00:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 12:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 12:30:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 12:45:00', 1 UNION ALL
SELECT 'emp1001', '2010-12-01 13:00:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:15:00', 3 UNION ALL
SELECT 'emp1001', '2010-12-01 13:30:00', 0 UNION ALL
SELECT 'emp1001', '2010-12-01 13:45:00', 1
;with cte as (
select rn=ROW_NUMBER()over(order by StartTime),* from tbTest t
where not exists(select 1 from tbTest where RestType=t.RestType and StartTime=dateadd(MINUTE,-15,t.StartTime)))
select t1.employee, t1.StartTime, t1.RestType,isnull(DATEDIFF(MINUTE,t1.StartTime,t2.StartTime),15) as RestTimeLen
from cte t1 left join cte t2 on t1.rn+1=t2.rn
where t1.RestType<>0
/*
employee StartTime RestType RestTimeLen
emp1001 2010-12-01 08:00:00.000 1 45
emp1001 2010-12-01 09:15:00.000 2 30
emp1001 2010-12-01 09:45:00.000 3 30
emp1001 2010-12-01 10:15:00.000 1 30
emp1001 2010-12-01 12:15:00.000 3 15
emp1001 2010-12-01 12:30:00.000 1 30
emp1001 2010-12-01 13:00:00.000 3 30
emp1001 2010-12-01 13:45:00.000 1 15
*/
select
a.employee ,a.StartTime ,a.RestType
,DATEDIFF(mi,a.StartTime ,b.StartTime )+ 15
from tbTest a ,tbTest b
where isnull((
select top 1 RestType
from tbTest
where employee= a.employee
and StartTime < a.StartTime
order by StartTime desc
),100) <> a.RestType
and isnull((
select top 1 RestType
from tbTest
where employee= b.employee
and StartTime > b.StartTime
order by StartTime asc
),100) <> b.RestType
and a.employee =b.employee
and a.RestType = b.RestType
and a.RestType <> 0
and a.StartTime <=b.StartTime
and not exists (
select 1
from tbTest c
where c.employee= a.employee
and c.StartTime > a.StartTime
and c.StartTime < b.StartTime
and c.RestType <> a.RestType
)
结果:
employee StartTime RestType
-------------------- ----------------------- ----------- -----------
emp1001 2010-12-01 08:00:00.000 1 45
emp1001 2010-12-01 09:15:00.000 2 30
emp1001 2010-12-01 09:45:00.000 3 30
emp1001 2010-12-01 10:15:00.000 1 30
emp1001 2010-12-01 12:15:00.000 3 15
emp1001 2010-12-01 12:30:00.000 1 30
emp1001 2010-12-01 13:00:00.000 3 30
emp1001 2010-12-01 13:45:00.000 1 15
(8 行受影响)
select
a.employee ,a.StartTime ,a.RestType
,DATEDIFF(mi,a.StartTime ,b.StartTime )
from tbTest a ,tbTest b
where isnull((
select top 1 RestType
from tbTest
where employee= a.employee
and StartTime < a.StartTime
order by StartTime desc
),100) <> a.RestType
and isnull((
select top 1 RestType
from tbTest
where employee= b.employee
and StartTime < b.StartTime
order by StartTime desc
),100) <> b.RestType
and a.employee =b.employee
and a.RestType <> b.RestType
and a.RestType <> 0
and a.StartTime <=b.StartTime
and not exists (
select 1
from tbTest c
where c.employee= a.employee
and c.StartTime > a.StartTime
and c.StartTime < b.StartTime
and c.RestType <> a.RestType
)
结果:
employee StartTime RestType
-------------------- ----------------------- ----------- -----------
emp1001 2010-12-01 08:00:00.000 1 45
emp1001 2010-12-01 09:15:00.000 2 30
emp1001 2010-12-01 09:45:00.000 3 30
emp1001 2010-12-01 10:15:00.000 1 30
emp1001 2010-12-01 12:15:00.000 3 15
emp1001 2010-12-01 12:30:00.000 1 30
emp1001 2010-12-01 13:00:00.000 3 30
(7 行受影响)
declare @n int,@state int
set @n=1
set @state=(select top 1 RestType from tbtest order by StartTime )
update tbtest
set @n=case when RestType=@state then @n else @n+1 end,@state=RestType,px=@n