mysql - 在 sql 查询结果中为 TOTAL 添加一行

Question

我有一个 SQL 表作为

empid  to_day     clock_in clock_out late_reason
001Acc 01/04/2016  9:12:08 18:33:57  Office work
001Acc 02/04/2016 10:12:08 19:33:57  Home work
001Acc 03/04/2016 11:12:08 20:33:57  Sick
001Acc 04/04/2016 10:12:08 19:53:57  Car disorder
001Acc 05/04/2016 13:12:08 19:33:57  Hospital

运行以下查询后:

SELECT
    to_day,
    (
        TIME_TO_SEC(clock_out) - TIME_TO_SEC(clock_in)
    ) AS worktime,
    late_reason
FROM
    myTable
WHERE
    emp_id = '001Acc'
AND (
    to_day BETWEEN "2016-04-01"
    AND "2016-04-05"
)

我可以得到

to_day     worktime late_reason
01/04/2016    33709 Office work
02/04/2016    33709 Home work
03/04/2016    33709 Sick
04/04/2016    34909 Car disorder
05/04/2016    22909 Hospital
***Total        158945***

(没有 TOTAL 行)。但是我想为查询中的总工作时间列添加一行(图中的红色部分)。可能吗？

请帮我重写查询。

谢谢。

Answer 1

个人而言，我认为此类问题最好留给应用程序级代码处理，但只是为了好玩......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(empid INT NOT NULL
,clock_in DATETIME NOT NULL
,clock_out DATETIME NULL
,late_reason VARCHAR(20) NULL
,PRIMARY KEY(empid,clock_in)
);

INSERT INTO my_table VALUES
(1 ,'2016/04/01  9:12:08','2016/04/01 18:33:57','Office work'),
(1 ,'2016/04/02 10:12:08','2016/04/02 19:33:57','Home work'),
(1 ,'2016/04/03 11:12:08','2016/04/03 20:33:57','Sick'),
(1 ,'2016/04/04 10:12:08','2016/04/04 19:53:57','Car disorder'),
(1 ,'2016/04/05 13:12:08','2016/04/05 19:33:57','Hospital');

SELECT COALESCE(date,'Total') date
     , diff 
  FROM 
     ( SELECT DATE(clock_in) date
            , SUM(TIME_TO_SEC(TIMEDIFF(clock_out,clock_in))) diff 
         FROM my_table 
        GROUP 
           BY date WITH ROLLUP
     ) x;
+------------+--------+
| date       | diff   |
+------------+--------+
| 2016-04-01 |  33709 |
| 2016-04-02 |  33709 |
| 2016-04-03 |  33709 |
| 2016-04-04 |  34909 |
| 2016-04-05 |  22909 |
| Total      | 158945 |
+------------+--------+