以下是一些适合您提供的文章的基于问题的标题： * 如何使用 LEFT JOIN 和子查询将学生主题合并到一条记录中？ * 创建统一的学生数据结构

在一个响应中返回多个响应数据

在上一个问题中，我问的是如何将所有学期的所有科目合并到一条记录中，以便每个学生应该有一个响应他的数据将像这个例子：

<code class="json">[
{
"userid": "1", - from users table
"username": "joe",  - from users table 
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "bio", - current subject name 
"activepts": "652", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
}
]</code>

所以我得到了一个且只有一个答案，但答案没有告诉我该怎么做，只是告诉我为什么我需要使用 LEFT JOIN并向我解释 LEFT JOIN 在我的情况下会做什么，所以我将再次解释我需要做什么，然后我将通过代码和一些示例解释如何做到这一点：

整个操作的要点：

1- 每个学生将有一个响应
2- 每个响应包含他的所有科目，每个科目将是一行
3- 第一行将包含该学生的一些静态数据：

• userid：users 表中用户的 id
• username：users 表中用户的名称
• subjectsid：第一个主题的 id该学生的
• 科目名称：该行现在的当前科目名称
• activepts：当前科目的分数
• totalpts：该学生在所有科目
• sem1，sem2，sem3：该学生每学期的总分

4-其余行与我从数据库得到的相同，无需更改一些内容，但过滤主题仅适用于该用户

执行此操作的代码：

<code class="php">$sql = 'SELECT 
                subjects.userid,
                users.name AS username,
                (
                    SELECT id 
                    FROM tbsubjects 
                    WHERE userid = subjects.userid 
                    ORDER BY id ASC 
                    LIMIT 1
                ) AS subjectsid,
                (
                    SELECT name 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        ORDER BY time DESC
                        LIMIT 1
                ) AS subjectname,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND month = DATE_FORMAT(NOW(), "%c")
                ) AS activepts,
                IFNULL(SUM(subjects.points), 0) AS totalpts,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 1
                ) AS sem1,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 2
                ) AS sem2,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM tbsubjects 
                    WHERE 
                        userid = subjects.userid 
                        AND semester = 3
                ) AS sem3 
            FROM 
                tbsubjects AS subjects 
                LEFT JOIN tbusers AS users ON users.id = subjects.userid 
            WHERE subjects.userid = :userid';

    $bindings = array(
        ':userid' => $userID,
    );

    $users = $statement->fetchAll(PDO::FETCH_OBJ);</code>

解释代码：
1- 字段在 SELECT 语句中，您可以看到它们分为 2 组，顶部的第一个是学生的静态字段，其余的将从表 tbsubjects
2 中获取值 - LEFT JOIN 函数负责获取表 tbsubjects 中的所有 (userid) 并通过它们之间的公共键 userid 合并它们，这意味着现在每个用户将由至少一行表示，其中包含他的第一个主题 ( ORDER BY id ASC )，然后是他的所有其他主题，但是它们已经合并在一行中，将在其余行中重复，作为学生的静态字段

输出示例：

<code class="json">[
{
"userid": "1", - from users table
"username": "joe",  - from users table 
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "bio", - current subject name 
"activepts": "652", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
},
{
"userid": "1", - from users table
"username": "joe",  - from users table 
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "phy", - current subject name 
"activepts": "10", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
},
{
"userid": "1", - from users table
"username": "joe",  - from users table 
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "math", - current subject name 
"activepts": "33", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
}
]</code>

以上是以下是一些适合您提供的文章的基于问题的标题： * 如何使用 LEFT JOIN 和子查询将学生主题合并到一条记录中？ * 创建统一的学生数据结构的详细内容。更多信息请关注PHP中文网其他相关文章！