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

この記事は約3分で読めます。

前提

tb_user_work_timeというDBがあります。

user_idwork_timework_date
108:00:002020-03-01
109:00:002020-03-02
212:00:002020-03-01
209:00:002020-03-02
308:00:002020-03-01
310:00:002020-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_idSUM(work_time – 80000) 
101:00:00
203:00:00
302: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_idSUM(work_time – 80000) 
203:00:00
302:00:00

となりますが、work_timeが8時間以下の時、値がおかしくなってしまいます。

tb_user_work_timeに8時間以下のデータがあった場合

user_idwork_timework_date
107:00:002020-03-01
108:00:002020-03-02
202:00:002020-03-01
209:00:002020-03-02
306:00:002020-03-01
310:00:002020-03-02
SELECT user_id, SUM(work_time) 
FROM `tb_user_work_time`
GROUP BY user_id
HAVING SUM(work_time - 80000) > 20000

結果

user_idSUM(work_time – 80000) 
1-01:00:00
2-05:00:00
300: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_idSUM(work_time – 80000) 
100:00:00
201:00:00
302:00:00

表示されるのは3だけ

バージョン

サーバのバージョン: 5.6.47 – MySQL Community Server (GPL)

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

コメント

タイトルとURLをコピーしました