回答編集履歴
1
TIME 型の計算方法を修正
answer
CHANGED
|
@@ -1,7 +1,10 @@
|
|
|
1
1
|
サブクエリを使わなくても、group by の結果に条件をつけるには having を使えばいいと思います。
|
|
2
2
|
|
|
3
|
-
|
|
3
|
+
修正: なお、MySQL では TIME 型の SUM は秒に変換して計算する必要があるようです。
|
|
4
|
+
参考: [MySQLのtime型の合計 - Qiita](https://qiita.com/niiyz/items/df45e886aa24d2fcaba3)
|
|
4
5
|
|
|
6
|
+
テーブル定義 (デバッグ用に user_id = 3 の work_date を変更しました。)
|
|
7
|
+
|
|
5
8
|
```sql
|
|
6
9
|
create table tb_user_work_time (
|
|
7
10
|
user_id int,
|
|
@@ -14,8 +17,8 @@
|
|
|
14
17
|
insert into tb_user_work_time values (1, '09:00:00', '2020-03-02');
|
|
15
18
|
insert into tb_user_work_time values (2, '12:00:00', '2020-03-01');
|
|
16
19
|
insert into tb_user_work_time values (2, '09:00:00', '2020-03-02');
|
|
17
|
-
insert into tb_user_work_time values (3, '08:
|
|
20
|
+
insert into tb_user_work_time values (3, '08:30:00', '2020-03-01');
|
|
18
|
-
insert into tb_user_work_time values (3, '
|
|
21
|
+
insert into tb_user_work_time values (3, '09:30:00', '2020-03-02');
|
|
19
22
|
```
|
|
20
23
|
|
|
21
24
|
問い合わせ
|
|
@@ -23,11 +26,11 @@
|
|
|
23
26
|
```sql
|
|
24
27
|
select
|
|
25
28
|
user_id,
|
|
26
|
-
sum(work_time - '
|
|
29
|
+
sec_to_time(sum(time_to_sec(work_time) - time_to_sec('8:00:00'))) as total_overtime
|
|
27
30
|
from tb_user_work_time
|
|
28
|
-
where work_time > '
|
|
31
|
+
where work_time > cast('8:00:00' as time)
|
|
29
32
|
group by user_id
|
|
30
|
-
having total_overtime >= '
|
|
33
|
+
having total_overtime >= cast('2:00:00' as time);
|
|
31
34
|
```
|
|
32
35
|
|
|
33
36
|
結果
|
|
@@ -36,7 +39,7 @@
|
|
|
36
39
|
+---------+----------------+
|
|
37
40
|
| user_id | total_overtime |
|
|
38
41
|
+---------+----------------+
|
|
39
|
-
| 2 |
|
|
42
|
+
| 2 | 05:00:00 |
|
|
40
|
-
| 3 |
|
|
43
|
+
| 3 | 02:00:00 |
|
|
41
44
|
+---------+----------------+
|
|
42
45
|
```
|