mysql获取多张表中的数据_如何从mysql中的两个表中获取数据？

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

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 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;

I was trying to return the required out from the following code but it does not return same.

Any idea why it is not returning the right values.I am a beginner in MySql so I am unable to find out the problem.

SELECT class.Name , COUNT(student.name)

From class INNER JOIN student

ON class.Fields_ID=student.Fields_ID;

解决方案

Using aggregate functions you should group them like GROUP BY class.Name so you will get the count of students in each class,other wise you will get a single row not the results per group i.e students per class

SELECT class.Name , COUNT(student.name)

From class INNER JOIN student

ON class.Fields_ID=student.Fields_ID

GROUP BY class.Name;