Re: [SQL ] (考題類)查詢的問題
我怎麼記得高考資料庫考的是這個？
Book (callNo,isbn,title,subject)
Patron(pId,name,startDate)
BorrowRecord(pId,callNo,borrowDate,returnDate)
1.找出張三(為一讀者姓名)所借過的所有書的分類號和書名,一本書只能列出一次
2.請列出現電腦類(即 subject='Computer') 每一本書籍的 ISBN,書名和被借閱的
次數請用一個SQL敘述表達
3.對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總
次數
因為剛好我的書附錄就有寫到這題 ^^
第一題我是用 group by + inner join 解決
第二題我是用次查詢
select b.isbn,Book.title,b.counts from (
select isbn,COUNT(isbn) as counts from BorrowRecord inner join book
on book.callno = borrowrecord.callno
where subject='computer'
group by isbn ) as b
inner join Book
on b.isbn = Book.isbn
第三題也是用次查詢
select p.pId,count(*) as 借閱總次數 from
(
select B.pId,B.borrowDate as counts from
(
select pId,COUNT(*) as counts from BorrowRecord
group by pId
having count(*) >=10
) as a
inner join Borrowrecord as B
on a.pId = B.pId
where B.borrowDate between '2009/1/1' and '2009/12/31'
) as p
group by p.pId
--
昔日所為 今日所受
今日所為 明日所受
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 61.221.67.170
→
08/27 16:35, , 1F
08/27 16:35, 1F
→
08/27 16:52, , 2F
08/27 16:52, 2F
→
08/27 19:42, , 3F
08/27 19:42, 3F
→
08/27 19:43, , 4F
08/27 19:43, 4F
討論串 (同標題文章)
完整討論串 (本文為第 2 之 10 篇)：