如何从mysql中的两个表中获取数据? [英] how to fetch data from two tables in 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;

我试图从下面的代码中返回所需的内容，但是没有返回相同的内容. 知道为什么它没有返回正确的值.我是MySql的初学者，所以我无法找出问题所在.

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;

推荐答案

使用汇总函数，您应该像GROUP BY class.Name那样对它们进行分组，这样您将获得每个班级的学生人数，否则，您将获得一行而不是一行每个小组的成绩，即每个班级的学生人数

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;

组(总计)函数

请参阅小提琴演示

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