按规则为学生图书借阅记录排序生成row_number列的实现方法

以下为适配MySQL 8.0及以上版本的实现代码，其他数据库仅需调整字符串截取、类型转换函数即可：

SELECT 
    Student_id,
    Book_id,
    class_id,
    `timestamp`,
    CASE 
        -- 同学生+同图书分组下所有时间均为空，统一行号为1
        WHEN MAX(`timestamp`) OVER (PARTITION BY Student_id, Book_id) IS NULL THEN 1
        -- 分组内存在有效时间，有值记录行号为1，空值记录行号为2
        ELSE ROW_NUMBER() OVER (
            PARTITION BY Student_id, Book_id 
            ORDER BY CASE WHEN `timestamp` IS NOT NULL THEN 0 ELSE 1 END ASC
        )
    END AS row_number
FROM 你的表名
-- 按图书数字部分从高到低排序输出
ORDER BY Student_id, CAST(SUBSTRING(Book_id, 3) AS UNSIGNED) DESC;

逻辑说明 #

• 规则1适配：使用SUBSTRING(Book_id, 3)截取图书ID第三位开始的数字部分，转整数后降序排序，保证数字大的图书排在前面。
• 规则2适配：按学生ID+图书ID分组，组内按时间是否非空排序生成行号，非空记录优先级更高。
• 规则3适配：先判断分组内是否存在有效时间，全部为空的情况下所有记录行号统一赋值为1。

如果用Python做数据处理，可参考以下代码：

import pandas as pd

# 读取数据，此处替换为你的数据读取逻辑
# df = pd.read_csv("你的数据集路径")

# 提取图书ID的数字部分
df["book_num"] = df["Book_id"].str[2:].astype(int)

# 按规则生成行号
df["row_number"] = df.groupby(["Student_id", "Book_id"]).apply(
    lambda group: [1] * len(group) if group["timestamp"].isna().all() 
    else group["timestamp"].isna().rank(method="min", ascending=True)
).reset_index(drop=True)

# 按要求排序，删除辅助列
df = df.sort_values(by=["Student_id", "book_num"], ascending=[True, False]).drop("book_num", axis=1)

# 输出结果
print(df)

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