如何用SQL将library_ledger操作应用到PostgreSQL的student_books表？

核心思路很直接：对每个学生的每本书，只看最后一次操作——如果最后一次是借，就保留；如果是还，就排除。全程用PostgreSQL原生窗口函数实现，不用自定义聚合，比数组聚合简单太多。

1. 筛选每本书的最后一次操作 #

先给每个学生的每本书标记出最新操作的序号：

SELECT
    student_id,
    book_id,
    operation,
    date,
    -- 按学生+书分组，最新操作排第1
    ROW_NUMBER() OVER (
        PARTITION BY student_id, book_id
        ORDER BY date DESC
    ) AS rn
FROM library_ledger

这里PARTITION BY student_id, book_id把数据按学生和书拆分，ORDER BY date DESC让最新的操作排在最前面，ROW_NUMBER()给每组的行编号，第一行就是该学生对这本书的最后一次操作。

2. 生成当前有效借阅集合 #

基于上面的结果，只保留最后一次操作为“借”的记录，就是学生当前的借阅列表：

WITH latest_ops AS (
    SELECT
        student_id,
        book_id,
        operation,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, book_id
            ORDER BY date DESC
        ) AS rn
    FROM library_ledger
)
SELECT student_id, book_id
FROM latest_ops
WHERE rn = 1 AND operation = 'borrow'

3. 更新student_books表（PostgreSQL 14适配） #

如果要同步更新student_books，可以分两步：先删无效记录，再插入新增借阅：

-- 删除已归还的书籍
DELETE FROM student_books
WHERE (student_id, book_id) NOT IN (
    WITH latest_ops AS (
        SELECT
            student_id,
            book_id,
            operation,
            ROW_NUMBER() OVER (
                PARTITION BY student_id, book_id
                ORDER BY date DESC
            ) AS rn
        FROM library_ledger
    )
    SELECT student_id, book_id
    FROM latest_ops
    WHERE rn = 1 AND operation = 'borrow'
);

-- 插入新增的借阅（避免重复）
INSERT INTO student_books (student_id, book_id)
WITH latest_ops AS (
    SELECT
        student_id,
        book_id,
        operation,
        ROW_NUMBER() OVER (
            PARTITION BY student_id, book_id
            ORDER BY date DESC
        ) AS rn
    FROM library_ledger
)
SELECT student_id, book_id
FROM latest_ops
WHERE rn = 1 AND operation = 'borrow'
ON CONFLICT (student_id, book_id) DO NOTHING;

为什么比数组聚合更简单？ #

• 不用折腾数组的增删逻辑，避免了数组操作的复杂度；
• 窗口函数是PostgreSQL原生功能，语法直观，后续维护成本低；
• 可以直接对接student_books的更新，不需要额外的中间转换步骤。

内容的提问来源于stack exchange，提问作者Frerich Raabe