请教高效SQL

songguozhi 2010-12-16 11:26:07
请教如下优化、效率高的SQL
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.可以使用表变量、临时表、函数、存储过程等
*/
...全文
100 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Rockets_NBA 2010-12-16
  • 打赏
  • 举报
回复
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 行受影响)
fpzgm 2010-12-16
  • 打赏
  • 举报
回复
高效

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
*/


昵称被占用了 2010-12-16
  • 打赏
  • 举报
回复
最后一条也出来了

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 行受影响)
昵称被占用了 2010-12-16
  • 打赏
  • 举报
回复
少最后一条结果,自己看看怎么添加上

这样的SQL做,效率只能先不考虑了

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 行受影响)
dawugui 2010-12-16
  • 打赏
  • 举报
回复
这个,用SQL做?难,帮顶.
songguozhi 2010-12-16
  • 打赏
  • 举报
回复
避免使用游标
[Quote=引用楼主 songguozhi 的回复:]
请教如下优化、效率高的SQL
SQL code
IF EXISTS(SELECT 1 FROM sys.tables WHERE name='tbTest')
DROP TABLE tbTest
CREATE TABLE tbTest(employee VARCHAR(20), --员工编号
StartTime DATETIME, --休息开始时间,都是按照刻钟为起点,格式为小……
[/Quote]

songguozhi 2010-12-16
  • 打赏
  • 举报
回复
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

这个写法太好了

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧