對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數



Book(callNo. isbn, title, subject)
//分類號，ISBN，書名，主題
Patron(pId, name, startDate)
//讀者編號，讀者姓名，開始日期
BorrowRecord(pId, callNo. borrowDate, returnData)
//讀者編號，分類號，借閱日期，歸還日期




對於每一位總借閱次數超過10(含)次的讀者,列出其讀者編號和2009年的借閱總次數

解答:


SELECT C.pId,IFNULL(Q2009,0) 2009borrow
FROM
(
     SELECT B.pId,COUNT(*) Q2009        /*借閱超過10(含)次 AND 2009年，的借閱的讀者人數*/
     FROM
                (SELECT * ,COUNT(*)
                 FROM BorrowRecord
                GROUP BY pId
                HAVING COUNT(*)>=10
               )AS A JOIN BorrowRecord B
                ON A.pId=B.pId
      WHERE B.borrowDate LIKE '%2009%'
      GROUP BY B.pId
) AS P 

     RIGHT JOIN
     (SELECT * ,COUNT(*) total          /*符合借閱次數超過10(含)次的讀者人數*/
     FROM BorrowRecord
     GROUP BY pId
     HAVING COUNT(*)>=10
     )AS C
     ON P.pId=C.pId


可能產生的表格:

留言

張貼留言

這個網誌中的熱門文章

Making HTTP GET Requests in JavaScript

透過Javascript實做Http 呼叫Api var xhr = new XMLHttpRequest(); xhr.open('GET', "http://localhost:25959/api/login?Acc=123123&Pw=AA", true); xhr.send(); xhr.onreadystatechange = processRequest; function processRequest(e) { if (xhr.readyState == 4 && xhr.status == 200) { var response = JSON.parse(xhr.responseText); console.log(response.Acc); } } readyState定義 Value State Description 0 UNSENT The open method hasn't been called yet 1 OPENED The send method has been called 2 HEADERS_RECEIVED The send method has been called and the HTTP request has returned the status and headers 3 LOADING The HTTP request response is being downloaded 4 DONE Everything has completed 文章： https://www.kirupa.com/html5/making_http_requests_js.htm
閱讀完整內容

Making HTTP POST Requests in JavaScript

透過Javascript實做Http 呼叫Api var http = new XMLHttpRequest(); var url = "http://localhost:25959/api/login"; var params = "acc=123123&pw=0958113359"; http.open("POST", url, true); //Send the proper header information along with the request http.setRequestHeader("Content-type", "application/x-www-form-urlencoded"); http.onreadystatechange = function() {//Call a function when the state changes. if(http.readyState == 4 && http.status == 200) { console.log(http.responseText); } } http.send(params); readyState定義 Value State Description 0 UNSENT The open method hasn't been called yet 1 OPENED The send method has been called 2 HEADERS_RECEIVED The send method has been called and the HTTP request has returned the status and headers 3 LOADING The HTTP request response is being downloaded 4 DONE Everything has completed 文章： https://www.kirupa.com/html5/making_http_requests_js.htm
閱讀完整內容