在上一个问题中,我问的是如何将所有学期的所有科目合并到一条记录中,以便每个学生应该有一个响应他的数据将像这个例子:
<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- 第一行将包含该学生的一些静态数据:
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中文网其他相关文章!