面试题：SQLite通过APSW接口在复杂查询中的应用

Question

假设有两个SQLite表，一个是`students`表，包含`id`、`name`、`class_id`字段；另一个是`classes`表，包含`id`、`class_name`字段。使用APSW接口编写Python代码实现查询每个班级的学生人数，并按学生人数从高到低排序。

Answer 1

import apsw

# 连接到SQLite数据库
conn = apsw.Connection('your_database.db')
cursor = conn.cursor()

# 执行SQL查询
query = """
SELECT classes.class_name, COUNT(students.id) AS student_count
FROM classes
LEFT JOIN students ON classes.id = students.class_id
GROUP BY classes.id
ORDER BY student_count DESC;
"""
cursor.execute(query)

# 获取结果
results = cursor.fetchall()

# 打印结果
for row in results:
    class_name, student_count = row
    print(f"班级: {class_name}, 学生人数: {student_count}")

# 关闭连接
conn.close()