mysql - 在一个查询中对另一个关系表求和并减去

Question

我有两个表，一个称为出勤，另一个称为时间片，我试图从时间片中减去出勤的总秒数 本周以及 Doctrine。我必须获取行，但我必须对查询中的每一行进行求和并减去，但我需要学会在一个查询中完成此操作。

这是出勤表的结构:

SELECT * FROM attendance;
+----+---------+---------------------+--------+---------------------+---------------------+
| id | user_id | day                 | status | check_in            | check_out           |
+----+---------+---------------------+--------+---------------------+---------------------+
|  1 |       1 | 2019-12-18 00:00:00 | end    | 2019-12-18 09:52:00 | 2019-12-18 23:37:02 |
|  2 |       1 | 2019-12-19 00:00:00 | end    | 2019-12-19 12:12:00 | 2019-12-19 21:05:00 |
+----+---------+---------------------+--------+---------------------+---------------------+

时间片表:

SELECT * FROM timeslice;
+----+---------------+-------------------------------------+---------------------+---------------------+---------------------+
| id | attendance_id | title                               | day                 | start_at            | stopped_at          |
+----+---------------+-------------------------------------+---------------------+---------------------+---------------------+
| 20 |             1 | Sacar al perro, ducharme y vestirme | 2019-12-18 00:00:00 | 2019-12-18 15:57:50 | 2019-12-18 12:15:36 |
| 21 |             1 | Dormir                              | 2019-12-18 00:00:00 | 2019-12-18 18:44:30 | 2019-12-18 16:16:44 |
| 22 |             1 | Descansar                           | 2019-12-18 00:00:00 | 2019-12-18 23:04:53 | 2019-12-18 20:56:29 |
| 23 |             2 | Comer                               | 2019-12-19 00:00:00 | 2019-12-19 16:03:00 | 2019-12-19 15:37:00 |
| 24 |             2 | Comer                               | 2019-12-19 00:00:00 | 2019-12-19 16:55:00 | 2019-12-19 16:17:00 |
| 25 |             2 | ducharme                            | 2019-12-19 00:00:00 | 2019-12-19 19:58:00 | 2019-12-19 17:20:00 |
+----+---------------+-------------------------------------+---------------------+---------------------+---------------------+

这是我当前的查询，我在其中获得结果，但随后我必须根据查询进行计算才能获得所需的结果和SQLFiddle: http://sqlfiddle.com/#!9/646be/3

SELECT SUM(TIME_TO_SEC(TIMEDIFF(a.check_out, a.check_in))) AS secondsAttendance
     , ( SELECT SUM(TIME_TO_SEC(TIMEDIFF(t.start_at, t.stopped_at))) 
           FROM timeslice t 
          WHERE t.attendance_id = a.id
       ) secondsPauses 
  FROM attendance a 
 GROUP 
    BY a.id

正如我之前所说，我需要的是能够在同一个查询中执行此操作，而无需使用 PHP 和 Doctrine

Answer 1

根据您的评论，我更改了我的答案。如果您只需要该值，那么您所需要做的就是使用初始查询(只需几次)作为FROM 子句 ( Derived Table ) 中的子查询，然后对其进行计算。在这种情况下，只需将秒数减去秒数暂停的结果与秒数相加，如下所示:

-- make the calculation you need over the results
SELECT SUM(Results.secondsAttendance - Results.secondsPauses) as ActualValue
FROM ( 
  -- use you initial results as a subquery and name it as Results
  SELECT 
  SUM(TIME_TO_SEC(TIMEDIFF(a.check_out, a.check_in))) AS secondsAttendance, 
  (SELECT SUM(TIME_TO_SEC(TIMEDIFF(t.start_at, t.stopped_at))) 
  FROM timeslice t WHERE t.attendance_id = a.id) AS secondsPauses 
  FROM attendance a 
  -- filter date for the current week 
  where yearweek(DATE(a.check_in), 1) = yearweek(curdate(), 1) 

  GROUP BY a.id
) Results; 

结果是:

+-------------+
| ActualValue |
+-------------+
| 38258       |
+-------------+

SqlFiddle in here