mysql连接重复行,顺序由问题引起

8ehkhllq  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(397)

我试着取每个班级的名字,这个班级的学生和分配给这个班级的科目。
在这里,问题是由 ORDER BY ,我已分配 ORDER BYstudent.id .
这会导致主题行重复。
如果 ORDER BY 不是assign,按预期获取主题行,不是重复行,而是学生行重复行。
表结构
学生

id  |   Rid |   class   |   name
================================
1   |   1   |   1       |   John
2   |   2   |   1       |   Harsita

id  |   title
==============
1   |   One
2   |   two
3   |   three

主题

id  |   title
===============
1   |   science
2   |   math
3   |   english
4   |   social
5   |   hpe

第二类科目

id  |   class   |   subject
===========================
1   |   1       |   1   
2   |   1       |   2
3   |   1       |   3
4   |   1       |   4

要求是

class: One
Science | Math | English | Social | Hpe | 
John | Harsita

但我得到的
如果 ORDER BY 分配给 student.id ```
class: One
Science | Math | English | Social | Hpe | Math | English | Social | Hpe | Science |
John | Harsita |

如果 `ORDER BY` 尚未分配给 `student.id` ```
class: One
Science | Math | English | Social | Hpe | 
John | Harsita | John | Harsita | John | Harsita | John | Harsita | John | Harsita |

我试过用 GROUP BYsubject.id ,它只显示一个学生(第一行:john)。问题出在哪里?我怎么能不使用 subquery 或者 GROUP_CONCAT 代码-php和mysql(带order by)

//mysql query
    SELECT 
        DISTINCT class_subject.class, 
        subject.title AS sub,
        student.name AS stdt,
        class.title AS class
    FROM 
        student
    INNER JOIN class_subject ON class_subject.class=student.class
    INNER JOIN subject ON subject.id=class_subject.subject
    INNER JOIN class ON class.id=student.class
    WHERE
        student.class=:cid;

//php
    $class='';
    $stdt='';
    $sub='';
    $results = Array();
        while($row=$result->fetch(PDO::FETCH_ASSOC)){
            if ($row['class'] != $class){
                $class = $row['class'];
                echo "Class: ".$class."<br/>";
            }
            if ($row['sub'] != $sub){
                $sub = $row['sub'];
                echo $sub." | ";
            }
            $results[]=$row;
        }
        echo "<br/>";
        foreach ($results as $row) {
            if ($row['stdt'] != $stdt) {
                $stdt = $row['stdt'];
                echo $stdt." | ";
            }
        }
djmepvbi

djmepvbi1#

查看预期结果sampel似乎需要name和subject的聚合结果

SELECT 
    group_concat( subject.title) AS sub,
    group_concat(student.name) AS stdt,
    class.title AS class
FROM 
    student
INNER JOIN class_subject ON class_subject.class=student.class
INNER JOIN subject ON subject.id=class_subject.subject
INNER JOIN class ON class.id=student.class
WHERE student.class=:cid
group by class.title

使用aggregation函数和group可以在同一行上显示聚合结果

uhry853o

uhry853o2#

你的问题是由 JOIN s。一班有四门课,两个学生。因此,您将在结果中得到4*2=8行。如您所见,联接的结果将是2组4个主题名,或4组2个学生名。您可以选择在php代码中处理此问题,或者由于您在php代码中有效地进行分组,请在查询中进行分组:

SELECT 
    c.title AS class,
    GROUP_CONCAT(DISTINCT s.title ORDER BY s.title SEPARATOR ' | ') AS subjects,
    GROUP_CONCAT(DISTINCT t.name ORDER BY t.name SEPARATOR ' | ') AS students
FROM class c
JOIN class_subject cs ON cs.class=c.id
JOIN subject s ON s.id=cs.subject
JOIN student t ON c.id=t.class
WHERE c.id=1
GROUP BY class

输出:

class   subjects                            students
One     english | math | science | social   Harsita | John

请注意,您可以指定 ORDER BY 内部 GROUP_CONCAT 控制组中值的顺序。

相关问题