MySQL时间计算时

Question

我有两个表，一个(个人工作)用于记录员工的工作时间，另一个表用于存储员工每小时赚多少钱(具体取决于工作时间)。员工可以根据工作时间享受不同的工时费率。

是否可以通过MySQL中的查询得到结果？

表 work_our 是否会按原样工作，或者我必须采取 time_to 永远不会 == time_from 来自同一员工的另一条记录？

个人工作

id | work_date  | time_from | time_to  | personal_id
 1 | 2017-01-01 | 16:00:00  | 22:00:00 | 3
 2 | 2017-01-01 | 11:00:00  | 12:00:00 | 6
 3 | 2017-01-01 | 15:00:00  | 02:00:00 | 10
 4 | 2017-01-01 | 20:00:00  | 00:00:00 | 12

工作时间

id | personal_id | valid_date_from | valid_date_to | time_from | time_to  | amount
 1 |           3 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 5.55
 2 |           3 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 7.77
 3 |           6 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 8.88
 4 |           6 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 5.55
 5 |          10 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 7.00
 6 |          10 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 10.00
 7 |          12 | 2013-01-01      |          null | 07:00:00  | 23:00:00 | 4.56
 8 |          12 | 2013-01-01      |          null | 23:00:00  | 07:00:00 | 7.89

结果

personal_id | period_from         | period_to           | paid
          3 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 33.30
          6 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 8.88
         10 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 86.00
         12 | 2017-01-01 07:00:00 | 2017-02-01 06:59:59 | 21.57

Answer 1

当您忽略 valid_date_from 和 valid_date_to 时，您可以使用:

SELECT lo1.personal_id,

       ROUND(SUM((TIME_TO_SEC(TIMEDIFF(lo1.end_datetime, lo1.start_datetime))/3600 - 
       TIME_TO_SEC(IF(lo1.start_datetime < lo2.start_datetime,TIMEDIFF(lo2.start_datetime, lo1.start_datetime),0))/3600 -
       TIME_TO_SEC(IF(lo1.end_datetime > lo2.end_datetime,TIMEDIFF(lo1.end_datetime, lo2.end_datetime),0))/3600) * amount), 2) AS to_pay
FROM
    (SELECT personal_id,
            ADDTIME(work_date, time_from) AS start_datetime,
            ADDTIME(IF(time_to > time_from, work_date, work_date + INTERVAL 1 DAY), time_to) AS end_datetime
     FROM personal_work) AS lo1
JOIN
    (SELECT personal_id,
            ADDTIME(work_date, time_from) AS start_datetime,
            ADDTIME(IF(time_to > time_from, work_date, work_date + INTERVAL 1 DAY), time_to) AS end_datetime,
            amount
     FROM
         (SELECT DISTINCT work_date
          FROM personal_work
          UNION SELECT max(work_date) + interval 1 DAY
          FROM personal_work AS work_date) AS dates_table
     JOIN work_hour) AS lo2 ON lo1.personal_id = lo2.personal_id
AND (lo1.start_datetime BETWEEN lo2.start_datetime AND lo2.end_datetime
     OR lo1.end_datetime BETWEEN lo2.start_datetime AND lo2.end_datetime)
GROUP BY 1