在score表中计算每个考试科目的平均成绩

假设在score表中，每行记录包含学生的姓名、考试科目和分数，你可以通过以下 SQL 语句计算每个考试科目的平均成绩：

SELECT subject, AVG(score) AS avg_score
FROM score
GROUP BY subject;

这个 SQL 查询语句使用了聚合函数 `AVG()` 来计算每个科目的平均分数。`GROUP BY` 语句则根据科目名称对分数进行分组，并对每个分组计算平均分数。执行这个查询语句后，你将会得到一个包含科目和平均分数的结果集。

相关问题

某班期末考试科目为数学、英语和计算机，有最多不超过30人参加考试，考试后要求： 1）计算每个学生的总分和平均分； 2）按总分成绩由高到低排出成绩的名次； 3）打印出名次表，表格内包括学生学号、各科分数、总分和平均分； 4）任意输入一个学号，能够查找出该学生在班级中的排名及其考试分数。 【提示】：用二维数组score存放每个学生各门课程的成绩，用一维数组num存放每个学生的学号，用一维数组sum存放每个学生的总分，用一维数组aver存放每个学生的平均分。

好的，这是一个典型的数组操作题，我给你提供一下思路：

1. 首先确定学生人数，通过输入或者手动设置均可；
2. 声明一个二维数组 score 用来存放每个学生的各科成绩；
3. 声明一个一维数组 num 用来存放每个学生的学号；
4. 声明一个一维数组 sum 用来存放每个学生的总分；
5. 声明一个一维数组 aver 用来存放每个学生的平均分；
6. 通过循环输入每个学生的学号和各科成绩，并计算出每个学生的总分和平均分；
7. 将每个学生的总分和平均分存入 sum 和 aver 数组中；
8. 对每个学生的总分进行排序，得到每个学生的名次；
9. 通过循环打印出每个学生的学号、各科分数、总分和平均分；
10. 通过循环查找出任意输入的学号在班级中的排名及其考试分数。

这里给出一个参考代码，你可以根据自己的实际情况进行修改：

import numpy as np

# 确定学生人数
n = int(input("请输入学生人数："))

# 声明数组
score = np.zeros((n, 3), dtype=np.int)  # 每个学生的各科成绩
num = np.zeros(n, dtype=np.int)  # 每个学生的学号
sum = np.zeros(n, dtype=np.int)  # 每个学生的总分
aver = np.zeros(n, dtype=np.float)  # 每个学生的平均分

# 输入每个学生的学号和各科成绩，并计算总分和平均分
for i in range(n):
    num[i] = int(input("请输入第%d个学生的学号：" % (i+1)))
    score[i, 0] = int(input("请输入第%d个学生的数学成绩：" % (i+1)))
    score[i, 1] = int(input("请输入第%d个学生的英语成绩：" % (i+1)))
    score[i, 2] = int(input("请输入第%d个学生的计算机成绩：" % (i+1)))
    sum[i] = np.sum(score[i])
    aver[i] = sum[i] / 3

# 对每个学生的总分进行排序，得到每个学生的名次
rank = np.argsort(-sum) + 1

# 打印出每个学生的学号、各科分数、总分和平均分
print("学号\t数学\t英语\t计算机\t总分\t平均分\t名次")
for i in range(n):
    print("%d\t%d\t%d\t%d\t%d\t%.2f\t%d" % (num[i], score[i, 0], score[i, 1], score[i, 2], sum[i], aver[i], rank[i]))

# 查找任意输入的学号在班级中的排名及其考试分数
search_num = int(input("请输入要查找的学生的学号："))
for i in range(n):
    if num[i] == search_num:
        print("该学生的排名为第%d名，总分为%d分。" % (rank[i], sum[i]))
        break
else:
    print("没有找到该学生的信息。")

使用C语言结构体来写一个程序某班有最多不超过30人（具体人数由键盘输入）参加期末考试，考试科目最多不超过6门（具体门数由键盘输入），定义结构体类型，用结构体数组作函数参数，编程实现如下学生成绩管理: (1）录入每个学生的学号、姓名和各科考试成绩;(2）计算每门课程的总分和平均分; (3）计算每个学生的总分和平均分; (4）按每个学生的总分由高到低排出名次表;(5)按每个学生的总分由低到高排出名次表;(6）按学号由小到大排出成绩表; (7）按姓名的字典顺序排出成绩表; (8）按学号查询学生排名及其各科考试成绩;(9）按姓名查询学生排名及其各科考试成绩; (10）输出每个学生的学号、姓名、各科考试成绩、总分、平均分，以及每门课程的总分和平均分。

以下是使用C语言结构体实现学生成绩管理的程序：

#include <stdio.h>
#include <string.h>

#define MAX_STUDENTS 30
#define MAX_SUBJECTS 6
#define MAX_NAME_LENGTH 20
#define MAX_ID_LENGTH 10

struct Subject {
    char name[MAX_NAME_LENGTH];
    int total_score;
    int num_students;
};

struct Student {
    char name[MAX_NAME_LENGTH];
    char id[MAX_ID_LENGTH];
    int scores[MAX_SUBJECTS];
    int total_score;
    float avg_score;
};

void input_students(struct Student students[], int num_students, int num_subjects) {
    printf("Enter student information:\n");
    for (int i = 0; i < num_students; i++) {
        printf("Student %d:\n", i+1);
        printf("Name: ");
        scanf("%s", students[i].name);
        printf("ID: ");
        scanf("%s", students[i].id);
        for (int j = 0; j < num_subjects; j++) {
            printf("Score for subject %d: ", j+1);
            scanf("%d", &students[i].scores[j]);
            students[i].total_score += students[i].scores[j];
        }
        students[i].avg_score = (float)students[i].total_score / num_subjects;
    }
}

void calculate_subject_totals(struct Subject subjects[], struct Student students[], int num_students, int num_subjects) {
    for (int j = 0; j < num_subjects; j++) {
        strcpy(subjects[j].name, "");
        subjects[j].total_score = 0;
        subjects[j].num_students = 0;
        for (int i = 0; i < num_students; i++) {
            subjects[j].total_score += students[i].scores[j];
            subjects[j].num_students++;
        }
        strcpy(subjects[j].name, "Subject ");
        char num[2];
        sprintf(num, "%d", j+1);
        strcat(subjects[j].name, num);
    }
}

void calculate_student_totals(struct Student students[], int num_students, int num_subjects) {
    for (int i = 0; i < num_students; i++) {
        students[i].total_score = 0;
        for (int j = 0; j < num_subjects; j++) {
            students[i].total_score += students[i].scores[j];
        }
        students[i].avg_score = (float)students[i].total_score / num_subjects;
    }
}

void sort_students_by_total_score_desc(struct Student students[], int num_students) {
    for (int i = 0; i < num_students-1; i++) {
        for (int j = i+1; j < num_students; j++) {
            if (students[i].total_score < students[j].total_score) {
                struct Student temp = students[i];
                students[i] = students[j];
                students[j] = temp;
            }
        }
    }
}

void sort_students_by_total_score_asc(struct Student students[], int num_students) {
    for (int i = 0; i < num_students-1; i++) {
        for (int j = i+1; j < num_students; j++) {
            if (students[i].total_score > students[j].total_score) {
                struct Student temp = students[i];
                students[i] = students[j];
                students[j] = temp;
            }
        }
    }
}

void sort_students_by_id(struct Student students[], int num_students) {
    for (int i = 0; i < num_students-1; i++) {
        for (int j = i+1; j < num_students; j++) {
            if (strcmp(students[i].id, students[j].id) > 0) {
                struct Student temp = students[i];
                students[i] = students[j];
                students[j] = temp;
            }
        }
    }
}

void sort_students_by_name(struct Student students[], int num_students) {
    for (int i = 0; i < num_students-1; i++) {
        for (int j = i+1; j < num_students; j++) {
            if (strcmp(students[i].name, students[j].name) > 0) {
                struct Student temp = students[i];
                students[i] = students[j];
                students[j] = temp;
            }
        }
    }
}

void print_student_ranking(struct Student students[], int num_students) {
    printf("Ranking by total score (descending):\n");
    printf("%-10s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n", "Rank", "Name", "ID", "Subject1", "Subject2", "Subject3", "Subject4", "Total");
    int rank = 1;
    for (int i = 0; i < num_students; i++) {
        if (i == 0 || students[i].total_score < students[i-1].total_score) {
            rank = i+1;
        }
        printf("%-10d%-20s%-10s", rank, students[i].name, students[i].id);
        for (int j = 0; j < MAX_SUBJECTS; j++) {
            printf("%-10d", students[i].scores[j]);
        }
        printf("%-10d%-10.2f\n", students[i].total_score, students[i].avg_score);
    }
}

void print_student_scores(struct Student students[], int num_students) {
    printf("Student scores:\n");
    printf("%-20s%-10s%-10s%-10s%-10s%-10s%-10s%-10s\n", "Name", "ID", "Subject1", "Subject2", "Subject3", "Subject4", "Total", "Average");
    for (int i = 0; i < num_students; i++) {
        printf("%-20s%-10s", students[i].name, students[i].id);
        for (int j = 0; j < MAX_SUBJECTS; j++) {
            printf("%-10d", students[i].scores[j]);
        }
        printf("%-10d%-10.2f\n", students[i].total_score, students[i].avg_score);
    }
}

void print_subject_scores(struct Subject subjects[], int num_subjects) {
    printf("Subject scores:\n");
    printf("%-20s%-10s%-10s\n", "Name", "Total", "Average");
    for (int j = 0; j < num_subjects; j++) {
        printf("%-20s%-10d%-10.2f\n", subjects[j].name, subjects[j].total_score, (float)subjects[j].total_score / subjects[j].num_students);
    }
}

void print_student_by_id(struct Student students[], int num_students, char id[]) {
    int rank = 0;
    for (int i = 0; i < num_students; i++) {
        if (strcmp(students[i].id, id) == 0) {
            rank = i+1;
            printf("Ranking for student %s:\n", id);
            printf("Rank: %d\n", rank);
            printf("Scores:\n");
            printf("%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s\n", "Name", "ID", "Subject1", "Subject2", "Subject3", "Subject4", "Total", "Average");
            printf("%-10s%-10s", students[i].name, students[i].id);
            for (int j = 0; j < MAX_SUBJECTS; j++) {
                printf("%-10d", students[i].scores[j]);
            }
            printf("%-10d%-10.2f\n", students[i].total_score, students[i].avg_score);
            return;
        }
    }
    if (rank == 0) {
        printf("No student with ID %s found.\n", id);
    }
}

void print_student_by_name(struct Student students[], int num_students, char name[]) {
    int rank = 0;
    for (int i = 0; i < num_students; i++) {
        if (strcmp(students[i].name, name) == 0) {
            rank = i+1;
            printf("Ranking for student %s:\n", name);
            printf("Rank: %d\n", rank);
            printf("Scores:\n");
            printf("%-10s%-10s%-10s%-10s%-10s%-10s%-10s%-10s\n", "Name", "ID", "Subject1", "Subject2", "Subject3", "Subject4", "Total", "Average");
            printf("%-10s%-10s", students[i].name, students[i].id);
            for (int j = 0; j < MAX_SUBJECTS; j++) {
                printf("%-10d", students[i].scores[j]);
            }
            printf("%-10d%-10.2f\n", students[i].total_score, students[i].avg_score);
            return;
        }
    }
    if (rank == 0) {
        printf("No student with name %s found.\n", name);
    }
}

int main() {
    int num_students, num_subjects;
    struct Student students[MAX_STUDENTS];
    struct Subject subjects[MAX_SUBJECTS];

    printf("Enter number of students (max %d): ", MAX_STUDENTS);
    scanf("%d", &num_students);
    printf("Enter number of subjects (max %d): ", MAX_SUBJECTS);
    scanf("%d", &num_subjects);

    input_students(students, num_students, num_subjects);
    calculate_subject_totals(subjects, students, num_students, num_subjects);
    calculate_student_totals(students, num_students, num_subjects);
    sort_students_by_total_score_desc(students, num_students);
    print_student_ranking(students, num_students);
    sort_students_by_total_score_asc(students, num_students);
    print_student_ranking(students, num_students);
    sort_students_by_id(students, num_students);
    print_student_scores(students, num_students);
    sort_students_by_name(students, num_students);
    print_student_scores(students, num_students);
    print_subject_scores(subjects, num_subjects);

    char id[MAX_ID_LENGTH];
    printf("Enter student ID to search: ");
    scanf("%s", id);
    print_student_by_id(students, num_students, id);

    char name[MAX_NAME_LENGTH];
    printf("Enter student name to search: ");
    scanf("%s", name);
    print_student_by_name(students, num_students, name);

    return 0;
}

以上程序实现了以下功能：

- 输入学生信息
- 计算每门课程的总分和平均分
- 计算每个学生的总分和平均分
- 将学生按总分从高到低排序并输出排名表
- 将学生按总分从低到高排序并输出排名表
- 将学生按学号从小到大排序并输出成绩表
- 将学生按姓名的字典顺序排序并输出成绩表
- 输出每个学生的学号、姓名、各科考试成绩、总分、平均分，以及每门课程的总分和平均分
- 按学号查询学生排名及其各科考试成绩
- 按姓名查询学生排名及其各科考试成绩。