mysql - 如何仅使用 MySQL 查询获取员工的休息时间

Question

id      staff_ID        STAFFNAME             CARDTIME
39618   1203024        BARAYUGA M.     2014-02-03 08:44:02
39618   1203024        BARAYUGA M.     2014-02-03 12:20:02
39618   1203024        BARAYUGA M.     2014-02-03 12:50:49
39618   1203024        BARAYUGA M.     2014-02-03 17:33:44
39622   1203056        LEONES M.       2014-02-03 12:00:21
39622   1203056        LEONES M.       2014-02-03 12:23:19
39622   1203056        LEONES M.       2014-02-03 13:22:33
39622   1203056        LEONES M.       2014-02-03 15:30:11 

上面是我的数据库中的表tbl_staff，有没有办法可以得到每个员工的总休息时间？仅使用 Mysql 查询。

这是我现在正在使用的示例查询。

SELECT 
  DATE,
  STAFFNAME, 
  LOGIN, LOGOUT, 
  SUCCESSFUL, 
  TIME,
  NUMBEROFTIME, 
  FIND_IN_SET(LOGIN,TIME),
  FIND_IN_SET(LOGOUT,TIME)
FROM 
(
    SELECT   
      DATE( CARDTIME ) AS DATE, 
      STAFFNAME,  
      MIN( CARDTIME )  AS LOGIN,  
      MAX( cardtime ) AS LOGOUT, 
      CASE 
        WHEN COUNT( CARDTIME ) %2 =0 THEN 1 
        ELSE 0 
      END AS  'SUCCESSFUL', 
      GROUP_CONCAT(DISTINCT(CARDTIME) ORDER BY (CARDTIME) )  AS TIME,
      COUNT(CARDTIME) as NUMBEROFTIME
    FROM tbl_staff
    GROUP BY STAFFNAME, DATE( CARDTIME )
) AS x

我已经研究了如何获得休息时间，但示例数据与我的不同，那里没有 LOGIN 和 LOGOUT。

预先感谢您的帮助。

Answer 1

MySQL 允许您编写这样的查询:

SELECT
  id, staff_ID, STAFFNAME,
  timediff(t3,t2) AS Break
FROM (
  SELECT
    id, staff_ID, STAFFNAME,
    DATE(CARDTIME) as carddate,
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        GROUP_CONCAT(CARDTIME order by CARDTIME),
        ',',
        3),
      ',',
      -1) t3,
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
      GROUP_CONCAT(CARDTIME order by CARDTIME),
      ',',
      2),
    ',',
    -1) t2
  FROM
    tablename
  GROUP BY
    id, staff_ID, STAFFNAME,
    DATE(CARDTIME)
  ) s

它不是太优化，也不是SQL标准，而且你还应该确保每天有四次卡片时间。但它应该会返回您需要的结果。

请参阅 fiddle here .

编辑

如果员工可以拥有少于或多于 4 个 cardtime 条目，您应该考虑使用此查询:

SELECT *
FROM (
  SELECT
    id,
    staff_ID,
    STAFFNAME,
    DATE(CARDTIME) AS card_day,
    timediff(next_CARDTIME,CARDTIME) As t_diff,
    CASE WHEN
      CASE WHEN next_CARDTIME IS NULL THEN @n:=-1 ELSE @n:=@n+1 END MOD 2 = 0
      THEN 'Work' ELSE 'Break'
    END AS type
  FROM (
    SELECT
      t1.id,
      t1.staff_ID,
      t1.STAFFNAME,
      t1.CARDTIME,
      MIN(t2.CARDTIME) next_CARDTIME
    FROM
      tablename t1 LEFT JOIN tablename t2
      ON (t1.id, t1.staff_ID) = (t2.id, t2.staff_ID)
         AND DATE(t1.cardtime)=DATE(t2.cardtime)
         AND t1.cardtime<t2.cardtime
    GROUP BY
      t1.id, t1.staff_ID, t1.STAFFNAME, t1.CARDTIME
    ORDER BY
      t1.id, t1.staff_ID, t1.STAFFNAME, t1.CARDTIME
    ) s, (SELECT @n:=-1) r
  ) s
WHERE t_diff IS NOT NULL

当然，如果 cardtime 条目的数量是奇数，则当天的最后一个条目将是一个休息时间。看看这个fiddle .