現有資料表

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

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

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 透過子查詢 (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

參考

• select - SQL join: selecting the last records in a one-to-many relationship - Stack Overflow