MIS 腳印 logo

MIS 腳印

記錄 IT 學習的軌跡

SQL JOIN 一對多關係,僅關聯最早、最後一筆日期時間記錄

SQL 將 JOIN 一對多關係的查詢結果,透過子查詢僅關聯最早、最後一筆日期時間記錄的查詢結果。

MySQL

現有資料表

假設現有 employees (員工) 和 clocks (打卡出勤記錄) 兩張 table 資料。

employees (員工)
id namt
E201308002 李四
E202003001 小美
clocks (打卡出勤記錄)
id in_time out_time employees_id (FK)
1 2020-08-25 07:52:00 2020-08-25 17:37:00 E201308002
2 2020-08-26 07:50:00 2020-08-26 17:08:00 E201308002
3 2020-08-26 07:53:00 2020-08-26 17:24:00 E202003001
4 2020-08-27 07:54:00 2020-08-27 17:10:00 E202003001
5 2020-08-30 07:41:00 2020-08-30 17:40:00 E202003001

僅關聯最早、最後一筆日期時間記錄

employees 和 clocks 是一對多 (one-to-many) 的關係,使用 JOIN 的查詢結果則會以 clocks 的資料數量取得對應的 rows。

SELECT
    employees.id AS employees_id,
    employees.name AS employees_name,
    clocks.id AS clocks_id,
    DATE_FORMAT(clocks.in_time, '%Y-%m-%d %H:%i') AS clocks_in_time,
    DATE_FORMAT(clocks.out_time, '%Y-%m-%d %H:%i') AS clocks_out_time
FROM employees
    LEFT JOIN clocks
        ON employees.id = clocks.employees_id
JOIN 查詢結果
employees_id employees_name clocks_id clocks_in_time clocks_out_time
E201308002 李四 1 2020-08-25 07:52 2020-08-25 17:37
E201308002 李四 2 2020-08-26 07:50 2020-08-26 17:08
E202003001 小美 3 2020-08-26 07:53 2020-08-26 17:24
E202003001 小美 4 2020-08-27 07:54 2020-08-27 17:10
E202003001 小美 5 2020-08-30 07:41 2020-08-30 17:40

JOIN 透過子查詢 (subquery) 來完成:

  1. 僅關聯 in_time 最早一筆日期時間記錄。
  2. 僅關聯 out_time 最後一筆日期時間記錄。
SELECT
    employees.id AS employees_id,
    employees.name AS employees_name,
    DATE_FORMAT(clocks_first.in_time, '%Y-%m-%d %H:%i') AS clocks_first_in_time,
    DATE_FORMAT(clocks_last.out_time, '%Y-%m-%d %H:%i') AS clocks_last_out_time
FROM employees

    -- 子查詢,僅關聯 in_time 最早一筆日期時間記錄
    LEFT JOIN (
        SELECT
            c1.in_time,
            c1.employees_id
        FROM employees
            JOIN clocks AS c1
                ON (employees.id = c1.employees_id)
            LEFT OUTER JOIN clocks AS c2
                ON (
                    employees.id = c2.employees_id
                    AND (
                        c1.in_time > c2.in_time
                        OR c1.in_time = c2.in_time
                        AND c1.id > c2.id
                    )
                )
        WHERE c2.id IS NULL
    ) AS clocks_first
        ON clocks_first.employees_id = employees.id

    -- 子查詢,僅關聯 out_time 最後一筆日期時間記錄
    LEFT JOIN (
        SELECT
            c1.out_time,
            c1.employees_id
        FROM employees
            JOIN clocks AS c1
                ON (employees.id = c1.employees_id)
            LEFT OUTER JOIN clocks AS c2
                ON (
                    employees.id = c2.employees_id
                    AND (
                        c1.out_time < c2.out_time
                        OR c1.out_time = c2.out_time
                        AND c1.id < c2.id
                    )
                )
        WHERE c2.id IS NULL
    ) AS clocks_last
        ON clocks_last.employees_id = employees.id
JOIN 透過子查詢僅關聯最早、最後一筆日期時間記錄查詢結果
employees_id employees_name clocks_first_in_time clocks_last_out_time
E201308002 李四 2020-08-25 07:52 2020-08-26 17:08
E202003001 小美 2020-08-26 07:53 2020-08-30 17:40

參考

發表迴響