Android学生成绩计算器APP：如何查询学生科目及对应成绩？

先修正表结构的关键问题 #

你的registercourse表创建语句中，声明了外键Course_StudentID，但未定义该字段，会导致外键关联失效，需要先补上字段定义：

sqLiteDatabase.execSQL("CREATE TABLE registercourse (" +
    "Course_ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
    "Course_StudentID INTEGER, " + // 补上关联学生ID的字段
    "course1 TEXT, " +
    "course2 TEXT, " +
    "course3 TEXT, " +
    "course4 TEXT, " +
    "course5 TEXT, " +
    "CONSTRAINT fk_registeruser FOREIGN KEY (Course_StudentID)"+ 
    " REFERENCES registeruser(ID)"+ 
    ");");

编写RAWQUERY查询语句 #

由于你的registercourse表是将5门科目存储在同一行的不同列中，需要先用UNION ALL将列转成行，再关联成绩表。以下是查询指定学生（以学生ID=1为例）的完整实现：

查询SQL与执行代码 #

// 构造查询SQL
String query = "SELECT " +
    "c.course_name AS 科目名称, " +
    "m.IA_Marks AS 平时成绩, " +
    "m.First_Term_Marks AS 期中成绩, " +
    "m.Second_Term_Marks AS 期末成绩, " +
    "m.Final_Exam_Marks AS 统考成绩, " +
    "m.Lab_Marks AS 实验成绩 " +
    "FROM ( " +
        "SELECT Course_ID, Course_StudentID, course1 AS course_name FROM registercourse WHERE Course_StudentID = ? " +
        "UNION ALL " +
        "SELECT Course_ID, Course_StudentID, course2 AS course_name FROM registercourse WHERE Course_StudentID = ? " +
        "UNION ALL " +
        "SELECT Course_ID, Course_StudentID, course3 AS course_name FROM registercourse WHERE Course_StudentID = ? " +
        "UNION ALL " +
        "SELECT Course_ID, Course_StudentID, course4 AS course_name FROM registercourse WHERE Course_StudentID = ? " +
        "UNION ALL " +
        "SELECT Course_ID, Course_StudentID, course5 AS course_name FROM registercourse WHERE Course_StudentID = ? " +
    ") AS c " +
    "LEFT JOIN registermarks m ON c.Course_ID = m.Marks_CourseID AND c.Course_StudentID = m.Marks_StudentID " +
    "WHERE c.course_name IS NOT NULL AND c.course_name != ''";

// 绑定学生ID参数（避免SQL注入）
String[] selectionArgs = new String[]{"1", "1", "1", "1", "1"};

// 执行查询
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.rawQuery(query, selectionArgs);

// 遍历结果
if (cursor.moveToFirst()) {
    do {
        String courseName = cursor.getString(cursor.getColumnIndex("科目名称"));
        float iaMarks = cursor.getFloat(cursor.getColumnIndex("平时成绩"));
        // 其他成绩字段可按相同方式获取
        // 处理数据逻辑...
    } while (cursor.moveToNext());
}

// 关闭资源
cursor.close();
db.close();

逻辑说明 #

• UNION ALL：将registercourse中一行的5个科目列转换为5行数据，实现和registermarks的一对一关联
• LEFT JOIN：保证即使科目未录入成绩，也能显示科目名称
• 参数绑定：用?占位符传递学生ID，避免SQL注入风险
• 空值过滤：排除未设置的空科目

可选优化：调整表结构 #

当前表设计中，registercourse用一行存5门科目的方式不够灵活，建议改成每门科目一行的结构：

CREATE TABLE registercourse (
    Course_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    Course_StudentID INTEGER,
    Course_Name TEXT,
    FOREIGN KEY (Course_StudentID) REFERENCES registeruser(ID)
);

调整后查询语句会大幅简化：

String query = "SELECT " +
    "c.Course_Name AS 科目名称, " +
    "m.IA_Marks, m.First_Term_Marks, m.Second_Term_Marks, m.Final_Exam_Marks, m.Lab_Marks " +
    "FROM registercourse c " +
    "LEFT JOIN registermarks m ON c.Course_ID = m.Marks_CourseID AND c.Course_StudentID = m.Marks_StudentID " +
    "WHERE c.Course_StudentID = ?";

String[] args = {"1"};
Cursor cursor = db.rawQuery(query, args);

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