第七章进阶的SQL.pptVIP

?黃三益2008 資料庫的核心理論與實務第四版 7-* 分群查詢句 分群彙總結合巢狀查詢 Q20：對於每一筆上網達成（即method = ‘cart’），且購買商品種樣數超過2的交易，列出其交易編號和所購買定價超過500的商品種類數。 SELECT tNo, COUNT(pNo) FROM (Transaction NATURAL JOIN Record) NATURAL JOIN Product WHERE method = ‘cart’ AND unitPrice 500 GROUP BY tNo HAVING COUNT(pNo) 2; WRONG! ?黃三益2008 資料庫的核心理論與實務第四版 7-* 分群查詢句 正確為： SELECT tNo, COUNT(pNo) FROM (Transaction NATURAL JOIN Record) NATURAL JOIN Product WHERE method = ‘cart’ AND unitPrice 500 AND tNo IN (SELECT tNo FROM Transaction NATURAL JOIN Record WHERE method = ‘cart’