MySQL三表聯合查詢操作舉例

2023-03-05 14:03:10

一、題目概述

給定三張表（學生表、科目表、成績表），查出各科成績最高的學生的資訊(學號、姓名、科目名稱、成績)。三張表下所示。

二、查詢思路

1.查詢出各科的最高成績

select max(s.`Score`) as MaxScore from score s group by s.`SubjectId`

2.查詢出各科的最高成績及對應的科目和學生編號(關鍵步驟)

此處通過子查詢完成。根據步驟1中查出的最高成績，再查成績表中成績等於最高成績且科目相對應的記錄

select b.* from (
   select max(s.`Score`) as MaxScore,s.`SubjectId` as SubId from score s group by s.`SubjectId`)
   f,score b where b.`SubjectId` = f.SubId and b.`Score` = f.MaxScore

該步驟的結果如下所示：

3. 查詢出各科的最高成績及對應的科目名稱和學生編號

步驟2只涉及了成績表，由於該表不含成績名稱，需要與科目表進行聯表查詢

SELECT b.`StudentId`,f.SubName,b.`Score` FROM (
   SELECT MAX(s.`Score`) AS MaxScore,s.`SubjectId` AS SubId,sub.`Name` AS SubName
   FROM score s LEFT JOIN SUBJECT AS sub ON s.`SubjectId` = sub.`SubjectNo` 
   GROUP BY s.`SubjectId`)f,score b WHERE b.`SubjectId` = f.SubId AND b.`Score` = f.MaxScore

該步驟的結果如下所示：

4. 查詢出各科的最高成績及對應的科目名稱和學生姓名

同理，再與學生表進行一次聯表查詢，查出學生姓名

SELECT stu.Name,t.* FROM 
    (SELECT b.`StudentId` AS Sid,f.SubName AS SubName,b.`Score` AS Score FROM 
         (SELECT MAX(s.`Score`) AS MaxScore,sub.`SubjectNo` AS SubNo,sub.`Name` AS SubName 
          FROM score s LEFT JOIN SUBJECT AS sub ON s.`SubjectId` = sub.`SubjectNo` GROUP BY sub.`SubjectNo`) 
    f,score b WHERE b.`StudentId` = f.SubNo AND b.`Score` = f.MaxScore) 
t LEFT JOIN student stu ON stu.StudentNo = t.Sid ORDER BY t.Score

最終結果如下：

三、錯誤總結

完成本題中遇到的一些錯誤，簡單總結以下

對group by的結果理解有問題。下面的SQL查詢嘗試查出最高成績對應的學生Id,

SELECT s.`StudentId` AS Sid,
MAX(s.`Score`) AS MaxScore,sub.`Name` AS SubName FROM score s LEFT JOIN SUBJECT AS sub ON s.`SubjectId` = 
sub.`SubjectNo` GROUP BY s.`SubjectId`

實際上SQL查出的學生Id都是3，即對應最高成績的Id是錯誤的。原因在於group by只是在select的查詢結果基礎上根據分組指定的關鍵字進行分組，並將分組後的若干記錄的中第一條進行展示，此處正好語數外分組的第一條記錄的學生Id都是3，因此是錯誤的。總結來說group by 只進行了分組，記錄中的其他欄位需要另外想辦法查詢。

附：sql 三個表聯合查詢（三表左聯查詢）

關於在一個表中顯示三個資料庫表欄位的查詢。

首先要找的三個表中欄位的對應關係，以哪一張表為主表（其他表欄位增添這個主表上）作為顯示頁面上。

上程式碼

查詢主表：csb_accessories_bjgh的全部，csb_accessories_bjqd表，csb_asset_directory表，的aname,atypes,/assetname,assettypes的欄位。

關係：csb_accessories_bjgh.accessoriesid = csb_accessories_bjqd.accessoriesid

以及 csb_accessories_bjgh.assetid = csb_asset_directory.assetid

這是sql程式碼

SELECT
csb_accessories_bjgh.*,
csb_accessories_bjqd.aname,
csb_accessories_bjqd.atypes,
csb_asset_directory.assetname,
csb_asset_directory.assettypes
FROM
(( csb_accessories_bjgh LEFT JOIN csb_accessories_bjqd ON csb_accessories_bjgh.accessoriesid = csb_accessories_bjqd.accessoriesid ) LEFT JOIN csb_asset_directory ON csb_accessories_bjgh.assetid = csb_asset_directory.assetid )
WHERE
csb_accessories_bjgh.id IS NOT NULL

總結

到此這篇關於MySQL三表聯合查詢操作的文章就介紹到這了,更多相關MySQL三表聯合查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL三表聯合查詢操作舉例

目錄

一、題目概述

二、查詢思路

三、錯誤總結

附：sql 三個表聯合查詢（三表左聯查詢）

總結

熱門文章