如何从三个或更多表中获取数据? [英] how to fetch data from three or more tables?

问题描述

我必须找出班级名称、每班学生人数和班级平均成绩等输出?我的两张桌子都是-

I have to find out the output as class name,number of students on each class and average marks in class? My both tables are-

 CREATE TABLE class(Fields_ID INT, Name VARCHAR(20));
        INSERT INTO class(Fields_ID,Name) VALUES(30,'FIRST');
        INSERT INTO class(Fields_ID,Name) VALUES(31,'SECOND');
        INSERT INTO class(Fields_ID,Name) VALUES(32,'THIRD');
        INSERT INTO class(Fields_ID,Name) VALUES(33,'FOURTH');
        INSERT INTO class(Fields_ID,Name) VALUES(34,'FIFTH');
SELECT * FROM class;

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 的初学者，所以我无法找出问题所在.

I was trying to return the required out from the following code but i was unable to form the logic for average marks. Any idea how to do it.I am a beginner in MySql so I am unable to find out the problem.

SELECT class.Name , COUNT(student.name),Marks.marks
From class INNER JOIN student INNER JOIN marks
    ON class.Fields_ID=student.Fields_ID=Marks.Fields_ID
GROUP BY class.Name;

推荐答案

  SELECT class.Name , COUNT(student.name),avg(Marks.marks) as average_mark
  From class 
  INNER JOIN student on class.Fields_ID = student.Fields_ID
  INNER JOIN marks  on marks.Fields_ID = student.Fields_ID
  GROUP BY class.Name;

这篇关于如何从三个或更多表中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！