阿摩線上測驗
題組內容
一、某書店資料庫中有數個資料表，其DDL如下，請回答下列問題：（3題，共16分）
(二)當某讀者把某作者的所有書都買過，我們稱該讀者為「鐵粉」。試以SQL語法，列出達文西(Author.name＝’達文西’)的所有「鐵粉」姓名。(3分)
詳解 (共 1 筆)
詳解
SELECT M.name AS "鐵粉姓名"
FROM Member M
JOIN Buy B ON M.mId = B.mId
JOIN Author A ON B.pNo = A.pNo
WHERE A.name = '達文西'
GROUP BY M.mId, M.name
HAVING COUNT(DISTINCT B.pNo) = (
SELECT COUNT(DISTINCT P.pNo)
FROM Author A
JOIN Product P ON A.pNo = P.pNo
WHERE A.name = '達文西'
);
FROM Member M
JOIN Buy B ON M.mId = B.mId
JOIN Author A ON B.pNo = A.pNo
WHERE A.name = '達文西'
GROUP BY M.mId, M.name
HAVING COUNT(DISTINCT B.pNo) = (
SELECT COUNT(DISTINCT P.pNo)
FROM Author A
JOIN Product P ON A.pNo = P.pNo
WHERE A.name = '達文西'
);