gpt4 book ai didi

MySQL 查询 : display all ungraded essays

转载 作者:行者123 更新时间:2023-11-29 01:00:46 25 4
gpt4 key购买 nike

到目前为止,我可以列出所有学生的论文和已评分的学生论文,我想知道如何才能显示所有未评分的论文?我还必须向我的 MySQL 代码添加什么?

这是我目前的代码。

SELECT students.*, students_essays.*  
FROM students
INNER JOIN students_essays ON students.student_id = students_essays.student_id
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
ORDER BY students_essays.id DESC

这是我的 MySQL 表。

CREATE TABLE students_essays (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE students (
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_first_name VARCHAR(255) DEFAULT NULL,
student_last_name VARCHAR(255) DEFAULT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (student_id)
);


CREATE TABLE essays_grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade_id INT UNSIGNED NOT NULL,
students_essays_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
letter_grade VARCHAR(2) DEFAULT NULL,
grade_points FLOAT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);

最佳答案

SELECT students.*, students_essays.*  
FROM students
INNER JOIN students_essays ON students.student_id = students_essays.student_id
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
INNER JOIN grades ON essays_grades.grade_id = grades.id
WHERE LETTER_GRADE IS NULL
ORDER BY students_essays.id DESC;

我使用了 INNER JOIN,就像您所做的一样。请注意,如果在右表中未找到匹配条目,则内部联接将隐藏左表中的行。如果您还想包括这些,请使用左连接。

如果文章未评分,我假设 letter_grade 为空。如果你的数据库没有 essays_grades 行,如果一篇文章没有评分,查询会更简单:

SELECT students.*, students_essays.*  
FROM students
INNER JOIN students_essays ON students.student_id = students_essays.student_id
LEFT JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
WHERE essays_grades.grade_id IS NULL
ORDER BY students_essays.id DESC;

关于MySQL 查询 : display all ungraded essays,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3427915/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com