在SQL的查詢語句select …. from …中，跟在from子句后面的通常是一張擁有定義的實(shí)體表，而有的時(shí)候我們會(huì)用子查詢來扮演實(shí)體表的角色，這個(gè)在from子句中的子查詢會(huì)返回一個(gè)結(jié)果集，這個(gè)結(jié)果集可以像普通的實(shí)體表一樣查詢、連接，這個(gè)子查詢的結(jié)果集就叫做衍生表。

一、衍生表簡(jiǎn)介

衍生表常用在需要對(duì)數(shù)據(jù)進(jìn)行臨時(shí)處理的場(chǎng)景，即對(duì)表直接查詢無法得出結(jié)果，需要對(duì)數(shù)據(jù)進(jìn)行加工，然后在加工基礎(chǔ)上與原數(shù)據(jù)再次進(jìn)行連接，才能得出結(jié)果。

示例數(shù)據(jù)準(zhǔn)備
例如下面一的張考試成績(jī)表，subject_id代表不同的科目，score代表分?jǐn)?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,'小紅',89), (null,1,'小橙',76), (null,1,'小黃',89),
 (null,1,'小綠',95), (null,2,'小青',77), (null,2,'小藍(lán)',83), (null,2,'小紫',99);

select * from exam;

1.1 衍生表基本用法

現(xiàn)要求：找出每個(gè)科目得分最高那條記錄，這個(gè)問題需要拆分成2步完成：

第一步：找出每個(gè)科目的最高分

select subject_id, max(score) score from exam group by subject_id;

第二步：將上一步的結(jié)果與exam表進(jìn)行連接，找出具體的記錄：

select e.* from exam e, (select subject_id, max(score) score from exam group by subject_id) d 
where d.subject_id=e.subject_id and d.score=e.score;

• 這里將第一步的查詢放在括號(hào)中，并取一個(gè)別名d。
• 通過別名d，MySQL可以像引用實(shí)體表一樣引用子查詢的結(jié)果集（衍生表）。

1.2 自定義列名

在給衍生表定義別名時(shí)，可以同時(shí)定義列名，方法是在別名后的括號(hào)內(nèi)列出列名，要注意列名的數(shù)量要和子查詢返回的列數(shù)量相同：

select a, b, d from (select 1,2,3,4) d(a,b,c,d);

1.3 衍生表的局限

衍生表目前的局限是它是一個(gè)獨(dú)立的子查詢，在生成結(jié)果集之前無法和from表中的其他表產(chǎn)生關(guān)聯(lián)，如果產(chǎn)生衍生表的子查詢成本非常高，而最后與其他表連接后只使用了一小部分?jǐn)?shù)據(jù)，那么這個(gè)性能浪費(fèi)是非常嚴(yán)重的。

例如上面的例子中，如果表中有10萬個(gè)科目，而我最終結(jié)果只涉及2個(gè)科目，那么在衍生表中對(duì)10萬個(gè)科目進(jìn)行g(shù)roup by顯然是沒有必要的，這種情況我們需要提前將外層謂語條件（where）傳入衍生表中，避免處理不必要的數(shù)據(jù)，但這也意味著謂語條件在外層寫了一遍，必須在衍生表中再寫一遍，增加了SQL復(fù)雜度。

在MySQL 8.0.14版本后，通過橫向衍生表（lateral關(guān)鍵字），可以在衍生表中引用from子句中之前出現(xiàn)的表，可以完美解決上述局限。
MySQL 橫向衍生表（Lateral Derived Tables）

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

最新評(píng)論