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

2020-08-29 腳印網頁資訊設計

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

現有資料表

假設現有 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) 來完成：

僅關聯 in_time 最早一筆日期時間記錄。
僅關聯 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