SQLのサブクエリを使って条件式を作る

Question

リンク
###前提
tb_user_work_timeというDBがあります。

user_id	work_time	work_date
1	07:00:00	2020-03-01
1	09:00:00	2020-03-02
2	12:00:00	2020-03-01
2	09:00:00	2020-03-02
3	08:00:00	2020-03-01
3	03:00:00	2020-03-02

このDBのユーザーごとに残業時間の合計時間を求めました。
ここでいう残業時間とは、1日に8時間を超えて働いた時間です。
なので以下のように、work_timeが8時間以上の時の、合計を算出しました。

SELECT user_id, SUM(work_time - 80000) 
FROM `tb_user_work_time`
WHERE `tb_user_work_time`.`work_time` > ‘80000’
GROUP BY user_id;

結果

user_id	SUM(work_time - 80000)
1	01:00:00
2	03:00:00
3	00:00:00

本題

これに対して、残業時間の合計時間SUM(work_time - 80000) が2時間以上になる時だけ、
表示をするようなSQLがわかりません。
work_timeはtime型で、8時間以下になる時もあります。

追加として必要な情報があれば、コメントしていただけると嬉しいです。

###バージョン
サーバのバージョン： 5.6.47 - MySQL Community Server (GPL)

Comments

[yambejp]

日付関数が関わるので、SQLの種類やバージョンを記載してください

[rikutennis]

追記しました！こちらで十分でしょうか？

[退会済みユーザー]

一応確認ですが、work_time のデータ型は time 型ですよね？

[rikutennis]

time型です！

Answer 1

質問の意図がわからないところがありますが、一応こんな感じで

Comments

[yambejp]

create table tb_user_work_time(user_id int,work_time time,work_date date);
insert into tb_user_work_time values
(1,'08:00:00','2020-03-01'),
(1,'09:00:00','2020-03-02'),
(2,'12:00:00','2020-03-01'),
(2,'09:00:00','2020-03-02'),
(3,'08:00:00','2020-03-01'),
(3,'10:00:00','2020-03-02');

[yambejp]

10時間以上の残業時間の集計

select user_id ,sum(case when work_time>'10:00:00' then timediff(work_time,'10:00:00') else '00:00:00' end) as time2
from tb_user_work_time
group by user_id

[hoshi-takanori]

いつも不思議なのですが、どうしてコードをコメント欄に書くのでしょうか？
回答欄にマークダウンを使って書いた方が読みやすいと思いますが。

[yambejp]

会社のセキュリティーポリシーで投稿禁止されてるからです
（少ない量のテキストや回答欄には制限がゆるいのでかけます）

IT会社の宿命ですね。
ちなみにGitHubにもアクセスできません

[hoshi-takanori]

なるほど…。失礼しました。

Answer 2

サブクエリを使わなくても、group by の結果に条件をつけるには having を使えばいいと思います。

修正: なお、MySQL では TIME 型の SUM は秒に変換して計算する必要があるようです。
参考: MySQLのtime型の合計 - Qiita

テーブル定義 (デバッグ用に user_id = 3 の work_date を変更しました。)

create table tb_user_work_time (
  user_id int,
  work_time time,
  work_date date,
  primary key (user_id, work_date)
);

insert into tb_user_work_time values (1, '08:00:00', '2020-03-01');
insert into tb_user_work_time values (1, '09:00:00', '2020-03-02');
insert into tb_user_work_time values (2, '12:00:00', '2020-03-01');
insert into tb_user_work_time values (2, '09:00:00', '2020-03-02');
insert into tb_user_work_time values (3, '08:30:00', '2020-03-01');
insert into tb_user_work_time values (3, '09:30:00', '2020-03-02');

問い合わせ

select
  user_id,
  sec_to_time(sum(time_to_sec(work_time) - time_to_sec('8:00:00'))) as total_overtime
from tb_user_work_time
where work_time > cast('8:00:00' as time)
group by user_id
having total_overtime >= cast('2:00:00' as time);

結果

+---------+----------------+
| user_id | total_overtime |
+---------+----------------+
|       2 | 05:00:00       |
|       3 | 02:00:00       |
+---------+----------------+

Comments

[rikutennis]

ありがとうございました。

[hoshi-takanori]

元のコードだと、work_time が例えば 8:30 と 9:30 で、残業時間の合計が 2 時間になるはずが、total_overtime が 16000 になってしまい、結果に出てこなかったので、計算方法を修正しました。

[rikutennis]

わざわざ修正いただきありがとうございました！！！