代码之家  ›  专栏  ›  技术社区  ›  Dipak

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

  •  0
  • Dipak  · 技术社区  · 6 年前

    我试着取每个班级的名字,这个班级的学生和分配给这个班级的科目。

    在这里,问题是由 ORDER BY ,我已分配 订货人 student.id .

    这导致 主题行复制 .

    如果 获取主题行 正如所料,它们不是重复的,而是 .

    表结构

    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 
    

    如果 订货人

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

    如果 尚未分配给 学生id

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

    GROUP BY subject.id ,它只显示一个学生(第一行:John)。问题出在哪里?我怎么能不使用 subquery GROUP_CONCAT

    //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." | ";
                }
            }
    
    2 回复  |  直到 6 年前
        1
  •  1
  •   ScaisEdge    6 年前

    查看预期结果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可以在同一行上显示聚合结果

        2
  •  1
  •   Nick SamSmith1986    6 年前

    JOIN

    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