优化一个考勤的SQL

baiqukqdp 2010-10-20 02:46:56
最近在做一个生成考勤报表,对写的SQL语句速率感觉不满意。
表结构如下:

CREATE TABLE [Attendance]
(
[usercode] [dbo].[varchar](30) NOT NULL,
[logdate] [datetime] NOT NULL,
[mac] [dbo].[varchar](30),
[tag1] [char](1),
[tag2] [char](1),
[tag3] [char](1)
)
GO
CREATE TABLE [AttendanceTime]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[ontime] [dbo].[varchar](30) NULL,
[offtime] [dbo].[varchar](30) NULL,
[iscurrent] [bit] NULL
)GO

AttendanceTime的数据:
id ontime offtime iscurrent
1 DOY 08:00:00 DOY 17:30:00 1
意思为上班时间08:00,下班时间17:30

Attendance是从文本文件BULK INSERT过来的。
文本文件原始数据为:
......
01992 20100825173022 05 1 0 2
01888 20100825173024 05 1 0 2
01888 20100825173024 05 1 0 2
20 05 1 0 2
02245 20100825173027 05 1 0 2
02285 20100825173028 05 1 0 2
......
文本文件修正后数据为:
......
01992 2010-08-25 17:30:22 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
01888 2010-08-25 17:30:24 05 1 0 2
02245 2010-08-25 17:30:27 05 1 0 2
02285 2010-08-25 17:30:28 05 1 0 2
......

开始插入语句:
BULK INSERT HrcmsDemo.dbo.Attendance
FROM 'C:\Att20100903.After.txt'
WITH (FIELDTERMINATOR ='\t')
GO
你可以从这里下载到此文件
我写的SQL:

WITH TotalAttendance AS(
SELECT usercode ,ontime ,CASE WHEN ontime=offtime THEN NULL ELSE offtime END offtime,COUNT(1) AS 'Count'
FROM (
SELECT a.usercode ,
(SELECT MIN(a1.logdate) FROM Attendance a1 WHERE a1.usercode=a.usercode
AND Year(a1.logdate)=Year(a.logdate) AND Month(a1.logdate)=Month(a.logdate) AND DAY(a1.logdate)=DAY(a.logdate)
) ontime ,
(SELECT MAX(a2.logdate) FROM Attendance a2 WHERE a2.usercode=a.usercode
AND Year(a2.logdate)=Year(a.logdate) AND Month(a2.logdate)=Month(a.logdate) AND DAY(a2.logdate)=DAY(a.logdate)
) offtime
FROM (SELECT usercode ,logdate FROM Attendance WHERE YEAR(logdate)='2010' and MONTH(logdate)='9' GROUP BY usercode ,logdate) AS a
) tt
GROUP BY usercode, ontime ,offtime
)
INSERT INTO ttt(usercode ,workday ,timeon ,timeoff ,ontime ,offtime)
SELECT usercode,
CONVERT(VARCHAR(10),h.ontime,120) workday,
CAST(REPLACE(a.ontime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeon ,
CAST(REPLACE(a.offtime,'DOY',CONVERT(VARCHAR(10),h.ontime,120)) AS DATETIME) timeoff ,h.ontime,h.offtime
FROM TotalAttendance h LEFT JOIN AttendanceTime a ON 1=1;

DECLARE @ont CHAR(5),@offt CHAR(5);
SET @ont='08:00'; SET @offt='17:30';
SELECT ''''+UserName UserName ,
''''+t.usercode usercode ,''''+workday workday,
''''+timeon timeon,''''+ontime ontime,
''''+timeoff timeoff,''''+offtime offtime ,
early ,late ,
CASE WHEN ontime IS NULL OR offtime IS NULL THEN '1' WHEN COALESCE(early,late,0) = 0 THEN '0' ELSE '1' END 'state'
FROM (
SELECT usercode ,workday ,@ont timeon ,dbo.fn_ParseTime(ontime) ontime ,@offt timeoff ,dbo.fn_ParseTime(offtime) offtime ,early ,late FROM ttt
) t
JOIN UserInfo AS ui ON t.usercode = ui.UserCode
ORDER BY t.usercode ,t.workday ;
...全文
81 9 点赞 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
baiqukqdp 2010-10-20
[Quote=引用 8 楼 fredrickhu 的回复:]
引用 7 楼 baiqukqdp 的回复:
引用 6 楼 dawugui 的回复:
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.

这个是,其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。


还是建议用索引 虽然牺牲了配置 但是可以明显的提高查询速度
[/Quote]

考勤数据是从文本导入的,用的是BULK INSERT,好像没有办法配置表的列
  • 打赏
  • 举报
回复
--小F-- 2010-10-20
[Quote=引用 7 楼 baiqukqdp 的回复:]
引用 6 楼 dawugui 的回复:
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.

这个是,其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。
[/Quote]

还是建议用索引 虽然牺牲了配置 但是可以明显的提高查询速度
  • 打赏
  • 举报
回复
baiqukqdp 2010-10-20
[Quote=引用 6 楼 dawugui 的回复:]
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.
[/Quote]
这个是,其实我在这里问的就是如何的没有索引的表快速地查找数据。

后期还是要在程序中作补充等等的工作的。
谢谢帮顶。
  • 打赏
  • 举报
回复
dawugui 2010-10-20
考勤系统仅仅用程序不能完善解决问题的,大多时候需要人工干预,帮顶.
  • 打赏
  • 举报
回复
baiqukqdp 2010-10-20
再次人工置顶。
  • 打赏
  • 举报
回复
baiqukqdp 2010-10-20
[Quote=引用 1 楼 xys_777 的回复:]
没怎么看懂。不过是考勤系统,建议每个月把完成的数据备份到历史里
[/Quote]
哪里看不懂?
这个考勤系统其实是 导入考勤机的数据,再生成员工的考勤统计。
  • 打赏
  • 举报
回复
sql_cctv 2010-10-20
看的眼花缭乱的
  • 打赏
  • 举报
回复
baiqukqdp 2010-10-20
此代码在没有重复数据的时候,生成一个月的考勤数据时间是8秒。
万望各位高手帮忙解答解答。

SQL 代码中的 ''''+ 只是为了导出EXCEL的时候转换为文本类型,保持 员工号00005 日期 2010-09-10 等等的形式。

还少了一个表结构

CREATE TABLE ttt
(
id INT IDENTITY(1,1),
usercode CHAR(5),
workday CHAR(10),
timeon DATETIME,
timeoff DATETIME,
ontime DATETIME,
offtime DATETIME,
early AS ( CASE WHEN DATEDIFF(n,timeon,ontime)<=0 THEN NULL ELSE DATEDIFF(n,timeon,ontime) END ),
late AS ( CASE WHEN DATEDIFF(n,offtime,timeoff)<=0 THEN NULL ELSE DATEDIFF(n,offtime,timeoff) END )
)


缺少USERINFO表可以不用。
  • 打赏
  • 举报
回复
永生天地 2010-10-20
没怎么看懂。不过是考勤系统,建议每个月把完成的数据备份到历史里
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-20 02:46
社区公告
暂无公告