先前 Day26 - 檢視表 ( View ) 會儲存在資料庫當中,但也可以將「建立檢視表用的 SELECT 敘述」直接寫在 SELECT 敘述中。如此一來便是個「查詢中的查詢」的概念,也就是子查詢( Subquery )。
以先前的商品分類總和檢視表為例,會寫成如下:
CREATE VIEW categorySum (name, countNum)
AS
SELECT category, COUNT(*)
FROM Product
GROUP BY category;
/*呼叫 categorySum*/
SELECT name, countNum FROM categorySum;
改用子查詢的寫法便是:
SELECT name, countNum
FROM (
SELECT category AS name, COUNT(*) AS countNum
FROM Product
GROUP BY category
)AS categorySum;
由於 SELECT 的敘述中,可以撰寫成如上的巢狀結構。故可以繞過 WHERE 子句中不可以使用如 AVG, MAX 之類的函數限制。但要注意的是其回傳值唯一筆或是多筆。
例如:查詢 Product 中大於所有產品平均售價的商品
SELECT id, name, category, sell_price
FROM Product
WHERE sell_price > (SELECT AVG(sell_price) FROM Product);
若有這樣的情況,需要將 兩筆 SELECT 查詢所參照的表格,在 subquery中利用 WHERE 條件來給予限制。
例如:查詢 Product 內,單筆產品大於其分類平均售價的
SELECT id, name, category, sell_price
FROM Product AS S1
WHERE sell_price > (
SELECT AVG(sell_price)
FROM Product AS S2
WHERE S1.category = S2.category
GROUP BY category
);