105年高考三級資料庫應用
|
三、考慮以下記載討論版文章的關聯： Member(mId, mName, email) // 會員(編號, 姓名, 電子郵件信箱) Post(pId, mId, dateTime, title, pContent) // 文章(文章id, 作者編號, 日期時間, 主旨, 內容) Reply(pId, rId, rmId, dateTime, rContent) // 回文(文章id, 回文id, 回文作者編號, 日期時間, 內容) 其中主鍵以底線表示，外鍵如下： Post(mId) 參考到 Member(mId) Reply(pId) 參考到 Post(pId) Reply(rmId) 參考到 Member(mId) 請用一個 SQL 查詢句 (Queries) 回答以下每一個查詢： (一)列出2016年有 PO 過文章 (即出現在 Post 關聯裡) 的會員之會員編號和姓名。（5分） (二)對於每一篇 PO 文，列出其 pId, PO 文時間，PO 文作者姓名，以及在2016年被回應的次數。（8分） (三)列出有原作者回應的 PO 文之 pId, title 和作者姓名。（7分） (四)找出PO 文中 (即出現在 Post 關聯裡) 的內容有疑似西元日期的文章之 pId, title, PO 文作者姓名。所謂疑似西元日期是指出現連續四個數字，且第一個數字為1或2，或是 (連續) 數字前有西元或公元的字眼。（10分） |
答：
Member表格：
|
mId |
mName |
|
|
M001 |
王一 |
email_1 |
|
M002 |
張二 |
email_2 |
|
M003 |
陳三 |
email_3 |
|
M004 |
吳四 |
email_4 |
Post表格：
|
mId |
mName |
|
|
M001 |
王一 |
email_1 |
|
M002 |
張二 |
email_2 |
|
M003 |
陳三 |
email_3 |
|
M004 |
吳四 |
email_4 |
Reply表格：
|
pId |
mId |
dateTime |
title |
pContent |
|
P001 |
M001 |
2016-07-18 |
標題1-1 |
內容1-1(西元1992) |
|
P002 |
M001 |
2016-07-18 |
標題1-2 |
內容1-2 |
|
P003 |
M002 |
2015-01-12 |
標題2-1 |
內容2-1(公元7年) |
|
P004 |
M002 |
2015-01-15 |
標題2-2 |
內容2-2(2004) |
|
P005 |
M003 |
2016-01-01 |
標題3-1 |
內容3-1(西元12年) |
(一)列出2016年有PO過文章的會員之會員編號和姓名
Select Distinct M.mId, M.mName
From Member As M, Post As P
Where M.mId = P.mId And Year(dateTime) = '2016'
執行結果：
|
mId |
mName |
|
M001 |
王一 |
|
M003 |
陳三 |
(二)
對於每一篇 PO 文，列出其 pId, PO 文時間，PO 文作者姓名，以及在2016年被回應的次數
Select P.pId, P.dateTime, M.mName, R2.count As 被回應的次數
From Member As M, Post As P,(
Select R.pId, Count(*) As count
From Reply As R Where Year(R.dateTime) = '2016'
Group By R.pId) As R2
Where M.mId = P.mId And P.pId = R2.pId
執行結果：
|
pId |
dateTime |
mName |
回應的次數 |
|
P001 |
2016-07-18 |
王一 |
2 |
|
P003 |
2015-01-12 |
張二 |
1 |
|
P004 |
2015-01-15 |
張二 |
1 |
(三)列出有原作者回應的PO文之pId, title和作者姓名
Select P.pId, P.title, M.mName
From Post As P, Member As M
Where P.mId = M.mId And Exists (
Select *
From Reply As R
Where R.pId = P.pId And R.rmId = P.mId)
執行結果：
|
pId |
title |
mName |
|
P001 |
標題1-1 |
王一 |
|
P003 |
標題2-1 |
張二 |
|
P004 |
標題2-2 |
張二 |
(四)
找出PO 文中 (即出現在 Post 關聯裡) 的內容有疑似西元日期的文章之 pId, title, PO 文作者姓名
Select P.pId, P.Title, M.mName
From Post As P, Member As M
Where P.mId = M.mId And (P.pContent Like '%[0-9][0-9][0-9][0-9]%'
OR P.pContent Like '%西元[0-9]%' OR P.pContent Like '%公元[0-9]%')
執行結果：
|
pId |
Title |
mName |
|
P001 |
標題1-1 |
王一 |
|
P003 |
標題2-1 |
張二 |
|
P004 |
標題2-2 |
張二 |
|
P005 |
標題3-1 |
陳三 |
※參考資料：
http://fecbob.pixnet.net/blog/post/38204901-sql%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%A9%A2%E8%AA%9E%E6%B3%95%E8%A9%B3%E8%A7%A3
留言列表
國考資訊處理解答 (7)
