在上一個問題中,我問的是如何將所有學期的所有科目合併到一筆記錄中,以便每個學生應該有一個回應他的數據將像這個例子:
<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中文網其他相關文章!