mysql判断是否迟到_在mysql中使用case和if条件的迟到和早期查询

下面是这个新的简化CASE

Fiddle的代码.这是旧的CASE

Fiddle.

– 实际上并不需要某些列,如果需要,可以将它们删除.

– 注意！我必须在每个需要根据部门条件设置的列中使用CASE.另外,请注意我已经使用了你在提问时提出的时间安排.如果您想要更改它们,请不要忘记在每个CASE语句中执行此操作,否则您将无法获得所需的结果.

SELECT e.emp_id, d.dept_name, e.emp_name,

CASE d.dept_name

WHEN 'Tech Support' THEN '08:00:00'

WHEN 'Network' THEN '13:00:00'

ELSE '09:00:00'

END AS `StartingTime`,

CASE d.dept_name

WHEN 'Tech Support' THEN '14:00:00'

WHEN 'Network' THEN '19:00:00'

ELSE '18:00:00'

END AS `EndingTime`,

TIME_FORMAT(el.login_time, '%T') AS `Entered_into_Office`,

TIME_FORMAT(el.logout_time, '%T') AS `Left_from_Office`,

CASE d.dept_name

WHEN 'Tech Support' THEN

TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('08:00:00', '%T')), '%T')

WHEN 'Network' THEN

TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('13:00:00', '%T')), '%T')

ELSE

TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('09:00:00', '%T')), '%T')

END AS `Time_in_diff`,

CASE d.dept_name

WHEN 'Tech Support' THEN

TIME_FORMAT(TIMEDIFF(TIME_FORMAT('14:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T')

WHEN 'Network' THEN

TIME_FORMAT(TIMEDIFF(TIME_FORMAT('19:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T')

ELSE

TIME_FORMAT(TIMEDIFF(TIME_FORMAT('18:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T')

END AS `Time_out_diff`,

CASE d.dept_name

WHEN 'Tech Support' THEN TIME_FORMAT(SEC_TO_TIME(

TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('08:00:00', '%T')), '%T'))

+TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('14:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T')

WHEN 'Network' THEN TIME_FORMAT(SEC_TO_TIME(

TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('13:00:00', '%T')), '%T'))

+TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('19:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T')

ELSE TIME_FORMAT(SEC_TO_TIME(

TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT(el.login_time, '%T'), TIME_FORMAT('09:00:00', '%T')), '%T'))

+TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(TIME_FORMAT('18:00:00', '%T'), TIME_FORMAT(el.logout_time, '%T')), '%T'))), '%T')

END AS `Total_time_diff`

FROM employees e

INNER JOIN department d ON d.dept_id = e.emp_dept

INNER JOIN employee_login el ON el.emp_id = e.emp_id

WHERE DATE_FORMAT(el.login_time, '%Y-%m-%d') BETWEEN '2012-01-01' AND '2013-12-31'

HAVING Total_time_diff > 0;

以下是我以前的解决方案：

在这个SQLFiddle中,我对你的范围进行了4次查询.这是一个部门的查询.我没有使用CASE条件,但它有效.每个时移一个,一个获取所有时间表的数据.对于后者我不得不删除这一行：ORDER BY Time_in_diff DESC;,以便UNION成功.如果有帮助,请告诉我！

这是solution with prepared statements.