Python操作SQLite3实现学生成绩同分同排名及间隙排名需求

你需要的同分同排名、后续名次跳号的规则属于SQL标准中的间隙排名逻辑，可通过以下两种方式实现：

方案1：使用SQLite原生窗口函数实现（推荐） #

SQLite 3.25.0及以上版本已支持标准窗口函数，对应规则刚好是RANK()函数的默认行为，无需将数据拉到Python侧循环处理，执行效率更高：

import sqlite3

conn = sqlite3.connect('students.sqlite')
cur = conn.cursor()

# 单条SQL完成排名计算与批量更新
cur.execute('''
UPDATE student_marks
SET Rank = (
    SELECT rnk FROM (
        SELECT Id, RANK() OVER (ORDER BY Marks DESC) AS rnk
        FROM student_marks
    ) t WHERE t.Id = student_marks.Id
)
''')
conn.commit()

# 验证结果 输出与你的预期完全一致
cur.execute('SELECT Id, Name, Marks, Rank FROM student_marks ORDER BY Rank')
print(cur.fetchall())
# 输出：[(1, 'Mark', 87, 1), (5, 'Ruth', 87, 1), (4, 'Cindy', 68, 3), (3, 'Adam', 65, 4), (2, 'John', 50, 5)]

conn.close()

方案2：旧版本SQLite兼容方案（Python侧计算排名） #

如果你的SQLite版本低于3.25.0不支持窗口函数，可以在Python侧先计算好分数与排名的映射关系，再批量更新：

import sqlite3

conn = sqlite3.connect('students.sqlite')
cur = conn.cursor()

# 1. 取出所有分数按降序排列
cur.execute('SELECT Marks FROM student_marks ORDER BY Marks DESC')
all_marks = [item[0] for item in cur.fetchall()]

# 2. 按规则生成分数-排名映射表
mark_rank_map = {}
current_rank = 1
for idx in range(len(all_marks)):
    if idx > 0 and all_marks[idx] != all_marks[idx-1]:
        current_rank = idx + 1
    if all_marks[idx] not in mark_rank_map:
        mark_rank_map[all_marks[idx]] = current_rank

# 3. 批量更新Rank字段
cur.executemany(
    'UPDATE student_marks SET Rank = ? WHERE Marks = ?',
    [(rank, mark) for mark, rank in mark_rank_map.items()]
)
conn.commit()
conn.close()

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