自關(guān)聯(lián)

設(shè)計(jì)省信息的表結(jié)構(gòu)provinces

id
ptitle

設(shè)計(jì)市信息的表結(jié)構(gòu)citys

id
ctitle
proid

citys表的proid表示城市所屬的省，對(duì)應(yīng)著provinces表的id值

問題：

能不能將兩個(gè)表合成一張表呢？

思考：

觀察兩張表發(fā)現(xiàn)，citys表比provinces表多一個(gè)列proid，其它列的類型都是一樣的

意義：

存儲(chǔ)的都是地區(qū)信息，而且每種信息的數(shù)據(jù)量有限，沒必要增加一個(gè)新表，或者將來還要存儲(chǔ)區(qū)、鄉(xiāng)鎮(zhèn)信息，都增加新表的開銷太大

答案：

定義表areas，結(jié)構(gòu)如下

id
atitle
pid

說明:

因?yàn)槭]有所屬的省份，所以可以pid可以填寫為null
城市所屬的省份pid，填寫省所對(duì)應(yīng)的編號(hào)id
這就是自關(guān)聯(lián)，表中的某一列，關(guān)聯(lián)了這個(gè)表中的另外一列，但是它們的業(yè)務(wù)邏輯含義是不一樣的，城市信息的pid引用的是省信息的id 在這個(gè)表中，結(jié)構(gòu)不變，可以添加區(qū)縣、鄉(xiāng)鎮(zhèn)街道、村社區(qū)等信息

創(chuàng)建areas表的語句如下：

create table areas( aid int primary key, atitle varchar(20), pid int );

查詢一共有多少個(gè)省

select count(*) from areas where pid is null;

例1：查詢省的名稱為“山西省”的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

例2：查詢市的名稱為“廣州市”的所有區(qū)縣

select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='廣州市';

總結(jié)：

自連接查詢其實(shí)等同于連接查詢，需要兩張表，只不過它的左表（父表）和右表（子表）都是自己。做自連接查詢的時(shí)候，是自己和自己連接，分別給父表和子表取兩個(gè)不同的別名，然后附上連接條件。

練習(xí)： 假想以下場(chǎng)景：某一電商網(wǎng)站想要對(duì)站內(nèi)產(chǎn)品做層級(jí)分類，一個(gè)類別下面有若干子類，子類下面也會(huì)有別的子類。例如數(shù)碼產(chǎn)品這個(gè)類別下面有筆記本，臺(tái)式機(jī)，智能手機(jī)等；筆記本，臺(tái)式機(jī)，智能手機(jī)又可以按照品牌分類；品牌又可以按照價(jià)格分類，等等。也許這些分類會(huì)達(dá)到一個(gè)很深的層次，呈現(xiàn)一種樹狀的結(jié)構(gòu)。那么這些數(shù)據(jù)要怎么在數(shù)據(jù)庫(kù)中表示呢？我們可以在數(shù)據(jù)庫(kù)中創(chuàng)建兩個(gè)字段來存儲(chǔ)id和類別名稱，使用第三個(gè)字段存儲(chǔ)類別的子類或者父類的id，最后通過自連接去查詢想要的結(jié)果。

創(chuàng)建表： create table tdb_cates( id smallint primary key auto_increment, cate_name varchar(20) not null, parent_id smallint not null );