快捷導(dǎo)航

MySQL 橫向衍生表(Lateral Derived Tables)的實(shí)現(xiàn)

更新時(shí)間：2025年06月11日 11:45:32 作者：V1ncent-CC

橫向衍生表適用于在需要通過(guò)子查詢獲取中間結(jié)果集的場(chǎng)景,相對(duì)于普通衍生表,橫向衍生表可以引用在其之前出現(xiàn)過(guò)的表名,本文就來(lái)介紹一下MySQL 橫向衍生表(Lateral Derived Tables)的實(shí)現(xiàn),感興趣的可以了解一下

前面我們介紹過(guò)MySQL中的衍生表（From子句中的子查詢）和它的局限性，MySQL8.0.14引入了橫向衍生表，可以在子查詢中引用前面出現(xiàn)的表，即根據(jù)外部查詢的每一行動(dòng)態(tài)生成數(shù)據(jù)，這個(gè)特性在衍生表非常大而最終結(jié)果集不需要那么多數(shù)據(jù)的場(chǎng)景使用，可以大幅降低執(zhí)行成本。

衍生表的基礎(chǔ)介紹：
MySQL 衍生表（Derived Tables）

一、橫向衍生表用法示例

橫向衍生表適用于在需要通過(guò)子查詢獲取中間結(jié)果集的場(chǎng)景，相對(duì)于普通衍生表，橫向衍生表可以引用在其之前出現(xiàn)過(guò)的表名，這意味著如果外部表上有高效的過(guò)濾條件，那么在生成衍生表時(shí)就可以利用這些條件，相當(dāng)于將外層的條件提前應(yīng)用到子查詢中。

先創(chuàng)建示例數(shù)據(jù)，有2張表，一張存儲(chǔ)科目和代課老師（Subject表），一張存儲(chǔ)學(xué)生的成績(jī)（Exam表）：

create table subject(
id int not null primary key,
sub_name varchar(12),
teacher varchar(12));

insert into subject values (1,'語(yǔ)文','王老師'),(2,'數(shù)學(xué)','張老師');

create table exam(
id int not null auto_increment primary key,
subject_id int,
student varchar(12),
score int);

insert into exam values(null,1,'小紅',87);
insert into exam values(null,1,'小橙',98);
insert into exam values(null,1,'小黃',89);
insert into exam values(null,1,'小綠',98);
insert into exam values(null,2,'小青',77);
insert into exam values(null,2,'小藍(lán)',83);
insert into exam values(null,2,'小紫',99);

select * from subject;
select * from exam;

在這里插入圖片描述

1.1 用法示例

現(xiàn)需要查詢每個(gè)代課老師得分最高的學(xué)生及分?jǐn)?shù)。這個(gè)問(wèn)題需要分為兩步：
1.通過(guò)對(duì)exam表進(jìn)行分組，找出每個(gè)科目的最高分?jǐn)?shù)。
2.通過(guò)subject_id和最高分?jǐn)?shù)，找到具體的學(xué)生和老師。

通過(guò)普通衍生表，SQL的寫(xiě)法如下：

select s.teacher,e.student,e.score
from subject s
join exam e on e.subject_id=s.id
join (select subject_id, max(score) highest_score from exam group by subject_id) best 
on best.subject_id=e.subject_id and best.highest_score=e.score;

在這里插入圖片描述

通過(guò)衍生表best，首先查出每個(gè)科目的最高最高分?jǐn)?shù)。
將衍生表與原表連接，查出最高分?jǐn)?shù)對(duì)應(yīng)的學(xué)生和老師。

橫向衍生表通過(guò)在普通衍生表前面添加lateral關(guān)鍵字，然后就可以引用在from子句之前出現(xiàn)的表，這個(gè)例子中具體的表現(xiàn)是在衍生表中和exam進(jìn)行了關(guān)聯(lián)。

select s.teacher,e.student,e.score
from subject s
join exam e on e.subject_id=s.id
join lateral (select subject_id, max(score) highest_score from exam where exam.subject_id=e.subject_id group by e.subject_id) best 
on best.highest_score=e.score;

在這里插入圖片描述

1.2 使用建議

橫向衍生表特性作為普通衍生表的補(bǔ)充，其本質(zhì)就是關(guān)聯(lián)子查詢，可以提升特定場(chǎng)景下查詢性能。

上面的案例中：

如果exam表非常大，而查詢結(jié)果僅涉及小部分科目，那么衍生表就沒(méi)必要對(duì)全部的exam數(shù)據(jù)進(jìn)行分組，利用橫向生成衍生表時(shí)可以直接過(guò)濾出這小部分科目的數(shù)據(jù)，然后再分組，此時(shí)橫向衍生表性能更優(yōu)。
如果exam表比較小，或者查詢結(jié)果覆蓋了大部分科目，那么一次性生成中間結(jié)果集成本更低，那么普通衍生表性能更優(yōu)。

到此這篇關(guān)于MySQL 橫向衍生表(Lateral Derived Tables)的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 橫向衍生表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: