到目前为止,我可以列出所有的学生论文,但我想能够只显示分级的学生论文,并想知道我如何才能做到这一点?我还需要在MySQL代码中添加什么?
以下是我的MySQL代码到目前为止得到的结果。

SELECT students.*, students_essays.*
FROM students
INNER JOIN students_essays ON students.student_id = students_essays.student_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
ORDER BY students_essays.id DESC

以下是一些提示。。。您可以给表名起别名(通过在FROM子句中的表名后面提供别名),并停止使用它们的全名来引用它们,您只需说JOIN,它就意味着是一个内部JOIN。
SELECT st.*, es.*
FROM students st
JOIN students_essays es ON st.student_id = es.student_id
JOIN essays_grades gr ON es.id = gr.students_essays_id
ORDER BY es.id DESC

10-08 02:22