前提
tb_user_work_timeというDBがあります。
user_id | work_time | work_date |
---|---|---|
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 |
この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 | 02:00:00 |
本題
これに対して、SUM(work_time – 80000) が2時間以上になる時だけ、表示をするようなSQLを、
上記のSQLのサブクエリとして発行するコードを知りたいです。
TRY
サブクエリについての調査
https://itsakura.com/sql-groupby-having
どうやらHAVINGの中に書くのが良さそう。
SELECT user_id, SUM(work_time) FROM `tb_user_work_time` GROUP BY user_id HAVING SUM(work_time - 80000) > 20000
とすると
user_id | SUM(work_time – 80000) |
---|---|
2 | 03:00:00 |
3 | 02:00:00 |
となりますが、work_timeが8時間以下の時、値がおかしくなってしまいます。
tb_user_work_timeに8時間以下のデータがあった場合
user_id | work_time | work_date |
---|---|---|
1 | 07:00:00 | 2020-03-01 |
1 | 08:00:00 | 2020-03-02 |
2 | 02:00:00 | 2020-03-01 |
2 | 09:00:00 | 2020-03-02 |
3 | 06:00:00 | 2020-03-01 |
3 | 10:00:00 | 2020-03-02 |
SELECT user_id, SUM(work_time)
FROM `tb_user_work_time`
GROUP BY user_id
HAVING SUM(work_time - 80000) > 20000
結果
user_id | SUM(work_time – 80000) |
---|---|
1 | -01:00:00 |
2 | -05:00:00 |
3 | 00:00:00 |
となるので全て2時間以下になり、表示は何もされません。
正解
SELECT user_id, SUM(work_time - '80000') AS total_overtime FROM tb_user_work_time WHERE work_time > '80000' GROUP BY user_id HAVING total_overtime >= '20000';
結果
user_id | SUM(work_time – 80000) |
---|---|
1 | 00:00:00 |
2 | 01:00:00 |
3 | 02:00:00 |
表示されるのは3だけ
バージョン
サーバのバージョン: 5.6.47 – MySQL Community Server (GPL)
追加として必要な情報があれば、コメントしていただけると嬉しいです。
コメント