問(wèn)題描述

剛學(xué)創(chuàng)建視圖,老是自己多做幾個(gè)實(shí)例,想提高一下,遇到一個(gè)問(wèn)題,我先大體解釋一下怎么回事:

表1 shbooks 里面是書籍,表2 shtype 里面是書籍的分類;表1的cid 正好 等于 表2的id;

我先用表2.cid=表1.id創(chuàng)建了一個(gè)視圖;

又想創(chuàng)建一個(gè)統(tǒng)計(jì)視圖,但是這個(gè)統(tǒng)計(jì)視圖是基于 shbooks 表的屬性用 GROUP BY cid , COUNT(*) 創(chuàng)建的,但是這個(gè)統(tǒng)計(jì)視圖沒(méi)有 書類目的名, 只有書類目的id?請(qǐng)問(wèn)怎么才能從 shtype 中把書類目名拿過(guò)來(lái)?

表1 shbooks:

idnamecidpricetime1挪威的森林186.852016-05-05 23:34:182經(jīng)濟(jì)學(xué)解釋223.112016-05-05 23:34:183麥田里的守望者153.872016-05-05 23:34:184伯羅奔尼撒戰(zhàn)爭(zhēng)336.022016-05-05 23:34:185老人與海198.92016-05-05 23:34:186海邊的卡夫卡157.212016-05-05 23:34:187人類群星閃耀時(shí)379.522016-05-05 23:34:188動(dòng)物農(nóng)場(chǎng)179.782016-05-05 23:34:18

表2 shtpye:

idtype1文學(xué)2經(jīng)濟(jì)3歷史4偵探

用表2.cid=表1.id創(chuàng)建了一個(gè)視圖;創(chuàng)建成功的創(chuàng)建的視圖:

主鍵書名類號(hào)類目?jī)r(jià)格導(dǎo)入時(shí)間1挪威的森林1文學(xué)86.852016-05-05 23:34:183麥田里的守望者1文學(xué)53.872016-05-05 23:34:185老人與海1文學(xué)98.92016-05-05 23:34:186海邊的卡夫卡1文學(xué)57.212016-05-05 23:34:188動(dòng)物農(nóng)場(chǎng)1文學(xué)79.782016-05-05 23:34:1810追憶似水年華1文學(xué)82.272016-05-05 23:34:1812舞舞舞1文學(xué)73.352016-05-05 23:34:182經(jīng)濟(jì)學(xué)解釋2經(jīng)濟(jì)23.112016-05-05 23:34:1813小麥經(jīng)濟(jì)學(xué)2經(jīng)濟(jì)15.192016-05-05 23:34:1814統(tǒng)計(jì)數(shù)字會(huì)說(shuō)謊2經(jīng)濟(jì)39.32016-05-05 23:34:184伯羅奔尼撒戰(zhàn)爭(zhēng)3歷史36.022016-05-05 23:34:18

現(xiàn)在這個(gè)視圖只有類目id,沒(méi)有類目名,我想在這個(gè)視圖集上加上類目名,就是可以顯示shbooks中cid在 shtype 中的 type,請(qǐng)問(wèn)怎么弄?

分類編號(hào)分類書籍總數(shù)17233134175262

創(chuàng)建語(yǔ)句如下:

CREATE OR REPLACE VIEW vcount AS SELECT cid AS 分類編號(hào),COUNT(*) AS 分類書籍總數(shù)FROM shbooksGROUP BY cid;

問(wèn)題解答

回答1：

CREATE OR REPLACE VIEW view_countAS SELECT t.id AS 書籍類目號(hào), t.type AS 書籍類目名, COUNT( b.name ) AS 書籍總數(shù), MIN( b.price ) AS 最低價(jià), MAX( b.price ) AS 最高價(jià)FROM shbook AS b, shtype AS tWHERE b.cid = t.idGROUP BY b.cid;