快捷導(dǎo)航

MySQL三表聯(lián)合查詢操作舉例

更新時(shí)間：2023年03月04日 11:58:54 作者：mingyuexinc

在mysql查詢語(yǔ)句中,為了實(shí)現(xiàn)查詢到某些信息,我們會(huì)用到多表的聯(lián)合查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL三表聯(lián)合查詢操作的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、題目概述

給定三張表（學(xué)生表、科目表、成績(jī)表），查出各科成績(jī)最高的學(xué)生的信息(學(xué)號(hào)、姓名、科目名稱、成績(jī))。三張表下所示。

圖片表

科目表

成績(jī)表

二、查詢思路

1.查詢出各科的最高成績(jī)

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

2.查詢出各科的最高成績(jī)及對(duì)應(yīng)的科目和學(xué)生編號(hào)(關(guān)鍵步驟)

此處通過(guò)子查詢完成。根據(jù)步驟1中查出的最高成績(jī)，再查成績(jī)表中成績(jī)等于最高成績(jī)且科目相對(duì)應(yīng)的記錄

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

該步驟的結(jié)果如下所示：

最高科目的學(xué)生信息

3. 查詢出各科的最高成績(jī)及對(duì)應(yīng)的科目名稱和學(xué)生編號(hào)

步驟2只涉及了成績(jī)表，由于該表不含成績(jī)名稱，需要與科目表進(jìn)行聯(lián)表查詢

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

該步驟的結(jié)果如下所示：

4. 查詢出各科的最高成績(jī)及對(duì)應(yīng)的科目名稱和學(xué)生姓名

同理，再與學(xué)生表進(jìn)行一次聯(lián)表查詢，查出學(xué)生姓名

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

最終結(jié)果如下：

最終結(jié)果

三、錯(cuò)誤總結(jié)

完成本題中遇到的一些錯(cuò)誤，簡(jiǎn)單總結(jié)以下

對(duì)group by的結(jié)果理解有問(wèn)題。下面的SQL查詢嘗試查出最高成績(jī)對(duì)應(yīng)的學(xué)生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`

實(shí)際上SQL查出的學(xué)生Id都是3，即對(duì)應(yīng)最高成績(jī)的Id是錯(cuò)誤的。原因在于group by只是在select的查詢結(jié)果基礎(chǔ)上根據(jù)分組指定的關(guān)鍵字進(jìn)行分組，并將分組后的若干記錄的中第一條進(jìn)行展示，此處正好語(yǔ)數(shù)外分組的第一條記錄的學(xué)生Id都是3，因此是錯(cuò)誤的?？偨Y(jié)來(lái)說(shuō)group by 只進(jìn)行了分組，記錄中的其他字段需要另外想辦法查詢。

附：sql 三個(gè)表聯(lián)合查詢（三表左聯(lián)查詢）

關(guān)于在一個(gè)表中顯示三個(gè)數(shù)據(jù)庫(kù)表字段的查詢。

首先要找的三個(gè)表中字段的對(duì)應(yīng)關(guān)系，以哪一張表為主表（其他表字段增添這個(gè)主表上）作為顯示頁(yè)面上。

上代碼

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

關(guān)系：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