mysql连接获取重复结果

7dl7o3gd 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(340)

我必须显示学生“约翰”的信息。
约翰的信息表-学生

id  | Roll | name  | class  | year 
==================================
1   |    1 | john  |     7  | 2

约翰的马克表-马克

id  |   std_id  |   sub_id  |   th  |   pr
===========================================
1   |   1       |   1       |   60  |   20
2   |   1       |   2       |   55  |   18

约翰的教育年表-年

id  |   title
=============
1   |   2017
2   |   2018

约翰的课桌-课堂

id  |   title
=============
7   |   Seven
8   |   Eight

约翰的主题表-主题

id  |   title
=============
1   |   Science
2   |   Math

现在我的要求如下

Name: John
Class: Seven
Year: 2018
Science: 60 (TH)
Math: 55 (TH)

但是，这里有重复的结果。我得到了约翰的信息，重复地数着所有的行 mark 这个学生的table。

Name: John
Class: Seven
Year: 2018
Science: 60 (TH)

Name: John
Class: Seven
Year: 2018
Math: 55 (TH)

我试过使用 GROUP BY 到std.id，以防止重复，但它只显示标记表的第一行。在这里，只有科学马克展示过。

$result=$con->prepare(
    "SELECT
        student.id, student.en_name AS name, student.class, 
        class.title AS class, 
        year.title AS year,
        subject.title AS sub,

        mark.sub_id,
        mark.th,
        mark.pr

        FROM student

        JOIN year ON year.id = student.year 
        JOIN class ON class.id = student.class 
        JOIN mark ON mark.std_id = student.id
        INNER JOIN subject ON subject.id = mark.sub_id
        WHERE student.id=:id;"
) or die($con->error);

    $result->bindParam(':id',$_POST['std']);
    $result->execute();
    while($row=$result->fetch(PDO::FETCH_ASSOC)){
        $name=$row['name'];
        $class=$row['class'];
        $sub=$row['sub'];
        $year=$row['year'];
        $th=$row['th'];
        $pr=$row['pr']; echo" 
        <article id='blg_half'>
            <article class='left'>
                Name: $name<br/>
                Class: $class<br/>
                Year: $year<br/>
                $sub : $th(th)
            </article>
        </article>";
    }

mysql php

来源：https://stackoverflow.com/questions/52090029/mysql-join-getting-duplicate-result

4条答案

按热度按时间

nwlqm0z11#

尝试内部连接，不需要根据您的问题分组

SELECT
        student.roll, student.name, student.address, student.img, student.class, student.section,
        class.title AS class, 
        section.title AS section, 
        year.title AS year,
        mark.id, mark.th, mark.pr
        FROM student 
        inner JOIN year ON year.id = student.year 
        inner class ON class.id = student.class 
        inner JOIN section ON section.id = student.section
        inner JOIN mark ON mark.std_id = student.id
group by student.roll, student.name, student.address, student.img, student.class, student.section,
        class.title AS class, 
        section.title AS section, 
        year.title AS year,
        mark.id, mark.th, mark.pr

赞(0）回复(0）举报 2021-06-21

hsgswve42#

根据你的最新问题，你实际上需要小组讨论来产生结果：

SELECT
    student.id, 
    student.roll, student.name, student.address, student.img, 
    class.title AS class,
    subject.title AS sub,
    `year`.title AS yr,
    GROUP_CONCAT(mark.th) AS th,
    GROUP_CONCAT(mark.pr) AS pr
FROM student 
JOIN `year` ON `year`.id = student.`year` 
JOIN class ON class.id = student.class 
LEFT JOIN mark ON mark.std_id = student.id
LEFT JOIN subject ON subject.id = mark.sub_id
WHERE student.id = :id 
GROUP BY student.id

mysql应该可以使用上述sql，但是标准sql应该按select中的所有字段分组，除了group\u concat（）
你也不能按…分组。。。当选择mark.id时——这毫无意义

赞(0）回复(0）举报 2021-06-21

ttygqcqt3#

试试这个

SELECT
        student.id, student.name,
        GROUP_CONCAT(CONCAT(subject.title,year.title,class.title,mark.th)) as Marks 
        FROM student
        LEFT JOIN year ON year.id = student.year 
        LEFT JOIN class ON class.id = student.class 
        LEFT JOIN mark ON mark.std_id = student.id
        LEFT JOIN subject ON subject.id = mark.sub_id
        WHERE student.id=:id
        GROUP BY student.id

赞(0）回复(0）举报 2021-06-21

a8jjtwal4#

尝试与标记表左联接：

SELECT 
student.roll, student.name, student.address, student.img, student.class, student.section,
class.title AS class, 
section.title AS section, 
year.title AS year,
mark.id, mark.th, mark.pr
FROM mark 
LEFT JOIN student ON student.id = mark.std_id   
LEFT JOIN year ON year.id = student.year 
LEFT JOIN class ON class.id = student.class 
LEFT JOIN section ON section.id = student.section
WHERE mark.std_id =:id

赞(0）回复(0）举报 2021-06-20

我来回答

mysql连接获取重复结果

4条答案

相关问题

热门标签

最新问答