在SQL查询结果中为TOTAL添加一行 [英] Add a row for TOTAL in a sql query result

问题描述

我有一个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行).但是我想为查询的总工作时间列添加一行(图像中为红色).有可能吗?

(without the TOTAL row). But I want to add a row for total of worktime column from the query (in red in the image). Is it possible?

请帮助我重写查询.

谢谢.

推荐答案

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

Personally, I think such problems are best left to application level code, but just for fun...

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 |
+------------+--------+

这篇关于在SQL查询结果中为TOTAL添加一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！