【SQLレシピ】同時刻のレコードでフラグが立ってるレコードを優先して選択する

SQLで同時刻のレコードでフラグが立ってるレコードを優先して選択したい！
フラグが立ってるレコードがない場合はフラグが立ってないレコードを選択したい！

概要

この記事では、SQLで同時刻のレコードでフラグが立ってるレコードを優先して選択する手順を掲載する。
同時刻にフラグが立ってるレコードがない場合はフラグが立ってないレコードがあればそのレコードを選択する！

仕様書

環境

• SQLite 3.35.5
• SQL Server 15.0.2000
• MariaDB 10.3.38-MariaDB-0ubuntu0.20.04.1

手順書

下表のような社員の施設への入退館を記録してるタイムカード的なテーブルtimecardを例にする。

登録してるデータは下記のとおり。

今回は、各社員が最後にどの施設に入退、または、退館したか抽出するクエリを紹介する。

入退と退館をフラグで管理してて、同時刻に入館のレコードがある場合はそのレコードを抽出する。入館のレコードが無い場合は退館のレコードを抽出する。別の施設に入館した際に自動的に前の施設を退館する仕組みの場合、このように同時刻に入退と退館のレコードが存在するケース。

SQLiteとSQL Server共通。

WITH CTE AS (
    SELECT
        distinct employee_id,
        id,
        location_id,
        is_entered,
        checked_at,
        ROW_NUMBER() OVER (PARTITION BY checked_at ORDER BY is_entered DESC) as rownum
    FROM
        timecard t
    WHERE
        t.checked_at=(SELECT MAX(checked_at) FROM timecard t_new WHERE t.employee_id = t_new.employee_id)
)
SELECT * FROM CTE WHERE rownum = 1
ORDER BY CTE.employee_id ASC

MySQL（MariaDB）の場合。

SELECT *
FROM (
    SELECT
        employee_id,
        id,
        location_id,
        is_entered,
        checked_at,
        ROW_NUMBER() over (partition BY checked_at ORDER BY is_entered DESC) AS rownum
    FROM timecard t
    WHERE t.checked_at=(SELECT MAX(checked_at) FROM timecard t_new WHERE t.employee_id = t_new.employee_id
    ) AS cte
WHERE cte.RowNum = 1
ORDER BY cte.employee_id ASC;

クエリを実行すると、下記のように同時刻に入館と退館のレコードがある場合は入館のレコードが優先されて各社員の最終のレコードの一覧が表示される。

まとめ（感想文）

施設の入出管理に使えるかもね！