mysql - 如何从多个表中获取数据？

Question

这个问题已经有答案了:

how to fetch data from two tables in mysql? (1 个回答)

已关闭 9 年前。

我必须找出获得超过 70 分的学生姓名的输出？我的两张 table 都是-

CREATE TABLE student(Fields_ID INT, Name VARCHAR(20));
        INSERT INTO student(Fields_ID,Name) VALUES(30,'JYOTI');
        INSERT INTO student(Fields_ID,Name) VALUES(31,'KIRTI');
        INSERT INTO student(Fields_ID,Name) VALUES(32,'YOGITA');
        INSERT INTO student(Fields_ID,Name) VALUES(33,'RASHMI');
        INSERT INTO student(Fields_ID,Name) VALUES(34,'NUPUR');
SELECT * FROM student;

CREATE TABLE Marks(Fields_ID INT, Student_ID INT NOT NULL,marks INT NOT NULL);
        INSERT INTO Marks(Fields_ID,Student_ID,Marks) VALUES (30,40,100);
        INSERT INTO Marks(Fields_ID,Student_ID,Marks) VALUES (31,41,88);
        INSERT INTO Marks(Fields_ID,Student_ID,Marks) VALUES (32,42,72);
        INSERT INTO Marks(Fields_ID,Student_ID,Marks) VALUES (33,43,33);
        INSERT INTO Marks(Fields_ID,Student_ID,Marks) VALUES (34,44,15);
SELECT * FROM Marks;

我试图从以下代码中返回所需的内容，但无法形成逻辑。知道如何做到这一点。我是 MySql 的初学者，所以我无法找出问题所在。

SELECT student.name,(select (marks>70) from marks)
From Student INNER JOIN marks  
    ON student.Fields_ID = marks.Fields_ID
GROUP BY student.name;

Answer 1

您可以使用having子句检查小组的条件。使用having来检查每个学生的总和是否达到了您的限制。

SELECT student.name
From Student 
INNER JOIN marks ON student.Fields_ID = marks.Fields_ID
GROUP BY student.name
HAVING sum(marks) > 70