mysql - 用于从相关表中提取数据的 SQL 查询

Question

虽然我发现了几个类似的问题，但我无法应用它们来解决我的问题。

问题陈述:- 我有三个 MySql 表 STUDENT、SUBJECT 和 STUDIES，如下所述:-

STUDENT-
rollno      Name
X1          Alpha
X2          Beta
Y1          Zeta
X3          Omega

这里每个 rollno 中的字母对应于同一批处理/类(class)的学生。例如学生 X1、X2 和 X3 属于同一个类(class)，而 Y1 是不同类(class)的学生。

SUBJECT-
Code     Title     Credits
abc     subject1     2
bcd     subject2     4
gfp     subject3     3



STUDIES-
rollno     code
X1          abc
X1          bcd
X1          gfp
X2          bcd
X2          abc
Y1          gfp
X3          abc

我需要帮助构建 mysql 查询:

a)显示每个学生修读的学分。喜欢

Rollno       Name      Credits
X1          Alpha       9
X2          Beta        6
Y1          Zeta        3
X3          Omega       2

我想出的最好的就是这个

select rollno, 
       (select sum(credits) from subject 
        where studies.code=subject.code)
      from studies;

但我得到的是学生学习的每个科目单独显示的 rollno 和学分。(我还无法扩展我的查询以从第三个表中获取学生的姓名)

b) 找出一个类(class)/批处理的所有学生已经选修的科目。在给定的场景中，答案是

Batch     Subject Code     Title
X           abc           subject1

我可以通过字符串处理提取出不同的批处理，但不知道如何进一步进行。

c) 作为 MySql 的新手，您能否也给我指出一些很好的网络资源，其中包含用于学习此类高级查询的练习题。在过去的几天里，我经历了其中的一些，但发现它们不足以形成解决我的查询所需的概念。

编辑:共享表的 CREATE 查询:-

对于主题:

CREATE TABLE IF NOT EXISTS subject (
code varchar(8) UNIQUE, title varchar(75) NOT NULL, 
credits int, check (credits <5),PRIMARY KEY (code));

对于学生:

CREATE TABLE IF NOT EXISTS student (
rollno varchar(9) UNIQUE,name varchar(50));

对于研究:

CREATE TABLE IF NOT EXISTS studies (
rollno varchar(9) NOT NULL,code varchar(50) NOT NULL,
FOREIGN KEY(rollno) REFERENCES student(rollno));

Answer 1

好吧，对于您的第一个查询，您想要连接所有三个表，查找 rollno、name 和 credits。因此，首先，您需要像这样加入:

SELECT s.rollno, s.name, sb.credits
FROM student s
INNER JOIN studies st
ON st.rollno = s.rollno
INNER JOIN subject sb
ON sb.code = st.code

这是解决方案的一部分 - 它为您提供所需的信息，现在您只需使用聚合函数来计算积分，使用 SUM 和 GROUP BY:

SELECT s.rollno, s.name, SUM(sb.credits) AS credits
FROM student s
INNER JOIN studies st
ON st.rollno = s.rollno
INNER JOIN subject sb
ON sb.code = st.code
GROUP BY s.rollno, s.name

第二部分更难，可能还有其他(和更好的)方法可以做到这一点，但这是我的方法:

SELECT q1.batch, q1.code, sb.title 
FROM
    (SELECT st.code, SUBSTR(st.rollno,1,1) batch,
     COUNT(SUBSTR(st.rollno,1,1)) numb
     FROM studies st
     GROUP BY st.code, SUBSTR(st.rollno,1,1)) q1
INNER JOIN
    (SELECT SUBSTR(s.rollno,1,1) batch, 
     COUNT(SUBSTR(s.rollno,1,1)) numb
     FROM student s
     GROUP BY SUBSTR(s.rollno,1,1)) q2
ON q1.batch = q2.batch AND q1.numb = q2.numb
INNER JOIN subject sb
ON q1.code = sb.code

一些解释:第一个子查询 (q1) 计算每个科目每个批处理的学生人数。输出将是:

abc    x    3
bcd    x    2
gfp    x    1
gfp    y    1

第二个子查询 (q2) 计算每批中的学生人数，输出:

x    3
y    1

通过连接这两个子查询，我们只选择那些批处理和批处理计数相同的主题:

abc    x    3
gfp    y    1

最后，在主题表上 JOIN 以获得包含的主题标题(并将起始 SELECT 语句设置为仅选择批处理、代码和标题)，给出输出:

x      abc  subj1
y      gfp  subj3

请注意，这里的最后一行 (y - subj3) 是有效的，因为“y”批处理(其中只有一个)的每个成员都注册了 gfp 类(class)。

至于推荐的站点和资源 - 这有点超出了 SO 的范围。您可以通过谷歌搜索“SQL 教程”或“SQL 在线类(class)”找到很多不错的在线资源。那里有很多免费的好东西。