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

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

一、橫向衍生表用法示例

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

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

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

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

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,'小藍',83);
insert into exam values(null,2,'小紫',99);

select * from subject;
select * from exam;

1.1 用法示例

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

通過普通衍生表，SQL的寫法如下：

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;

• 通過衍生表best，首先查出每個科目的最高最高分數(shù)。
• 將衍生表與原表連接，查出最高分數(shù)對應(yīng)的學生和老師。

橫向衍生表通過在普通衍生表前面添加lateral關(guān)鍵字，然后就可以引用在from子句之前出現(xiàn)的表，這個例子中具體的表現(xiàn)是在衍生表中和exam進行了關(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 使用建議

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

上面的案例中：

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

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

最新評論