MySQL : join three tables with cardinality 1:N

Question

我有一个学院的数据库。它的结构有三个表:- 第一个是学科硕士。该表可能有 20 多行。

CREATE TABLE `SUBJECTS` (
  `ID_SB` tinyint(4) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID_SB`)
) 

-第二个包含学生数据。这个可能有超过 10000 行。

CREATE TABLE `STUDENTS` (
  `ID_ST` int(10) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID_ST`)
) 

-最后一个有学生每个科目的资格。它有大约 1000 行。

CREATE TABLE `QUALIFICATIONS` (
  `ID_CF` int(10) NOT NULL AUTO_INCREMENT,
  `ID_ST` int(10) DEFAULT NULL,
  `ID_SB` int(10) DEFAULT NULL,
  `VALUE` int(2) DEFAULT NULL,
  PRIMARY KEY (`ID_CF`)
)

我需要将所有这些数据导出为 excel 格式，例如:

Id:st/Name/Subject1/Subject2/Subject3/Subject4/..../Subject_N(如果一个学生不具备某一科目的资格，excel 将为此列显示空单元格)。

如何连接所有这些表？我考虑过使用 LEFT JOIN，但此查询的返回行数呈指数增长。

现在我的问题比以前更糟，因为我必须添加与具有 1:N 基数的学生相关的新表。

我该如何改进此功能？我无法更改数据库架构。

谢谢

Answer 1

我想你忘记了 GROUP BY，请尝试以下操作。

SELECT
    ST.ID_ST, ST.`NAME`, GROUP_CONCAT(DISTINCT SU.`NAME`) AS subjects
FROM STUDENTS AS ST
LEFT JOIN QUALIFICATIONS AS QU ON
    ST.ID_ST = QU.ID_ST
LEFT JOIN SUBJECTS AS SU ON
    SU.ID_SB = QU.ID_SB
GROUP BY ST.ID_ST

这应该只显示每个学生一次，科目应该用逗号 (,) 分隔，每个科目每个学生应该只出现一次(但前提是学生有科目)。

ID - 姓名 - 主题
1 - 夏娃 - 主题 1、主题 3、主题 7

---

但是，这不是您要求的，您希望每个主题都有自己的专栏，对吗？

为此，您可以使用 subquery在选择中获取所有主题并使用 IF为每个主题打印是或否。您仍然需要使用 GROUP_CONCAT。但我会以不同的方式解决它。

我会使用不同的语言来分隔列。首先，您需要所有学生科目的学生信息和 ID，如下所示:

SELECT
    ST.ID_ST, ST.`NAME`, GROUP_CONCAT(DISTINCT QU.`ID_SB`) AS subject_ids
FROM STUDENTS AS ST
LEFT JOIN QUALIFICATIONS AS QU ON
    ST.ID_ST = QU.ID_ST
GROUP BY ST.ID_ST

然后获取主题:

SELECT * FROM SUBJECTS

最后一部分是创建 Excel。下面的示例是用 PHP 编写的:

$excelRows = array();
foreach($students as $student){
    $excelRow = array($student->id, $student->name);
    foreach($subjects as $subject){
        array_push($excelRow, (in_array($subject->id, $student->subject_ids))?'yes':'no');
    }
    array_push($excelRows, $excelRow);
}

所以，我们只是循环学生，在学生循环中我们循环主题。

---

它没有经过测试，但我认为它的大部分应该可以工作。如果失败了，请告诉我你尝试了什么，并解释为什么它不起作用。