以下是一些適合您提供的文章的基於問題的標題： * 如何使用 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中文網其他相關文章！