最近 一直在做公司的每日考勤这一块,用的是mysql 在写的时候遇到了不少的麻烦 其一在选取有效工作日时(attendance_time 存的是考勤数据,_atte_holidays 这里面存的是节假日的数据),我用了一条sql语句来查询
SELECT DISTINCT(DATE_FORMAT(tt.attendance_time,'%Y-%m-%d')) FROM t_attendance_time tt
WHERE DATE_FORMAT(tt.attendance_time,'%Y-%m-%d') BETWEEN '2011-02-04' AND '2011-03-04' AND
tt.attendance_time NOT IN(
SELECT DISTINCT(t.attendance_time) FROM t_attendance_time t,t_atte_holidays th WHERE
DATE_FORMAT(t.attendance_time,'%Y-%m-%d') BETWEEN DATE_FORMAT(th.date_start,'%Y-%m-%d') AND DATE_FORMAT(th.date_end,'%Y-%m-%d') AND
DATE_FORMAT(t.attendance_time,'%w') NOT IN ('0','6') )
AND DATE_FORMAT(tt.attendance_time,'%w') NOT IN ('0','6') ORDER BY tt.attendance_time DESC
结果是出来了,可这条语句执行起来超慢,受不了。经过我不断思考 把两个表并起来查:又写成了这样:
SELECT DISTINCT(DATE_FORMAT(tt.attendance_time,'%Y-%m-%d')) FROM t_attendance_time tt,t_atte_holidays th
WHERE DATE_FORMAT(tt.attendance_time,'%Y-%m-%d') BETWEEN '2011-02-04' AND '2011-03-04' AND
DATE_FORMAT(tt.attendance_time,'%Y-%m-%d') NOT BETWEEN DATE_FORMAT(th.date_start,'%Y-%m-%d') AND DATE_FORMAT(th.date_end,'%Y-%m-%d')
AND DATE_FORMAT(tt.attendance_time,'%w') NOT IN ('0','6')
ORDER BY tt.ATTENDANCE_TIME DESC
我想过了,两者的查询结果是一样的。后者查询速度是超快的。但对为啥会变快了 还是不太了解 有哪位对数据库熟悉 请你不吝赐教