112年法務部調查局調查人員三等資料庫應用
|
二、假設有如下團購紀錄關連式資料庫，請使用 SQL 回答下列相關問題。 （每小題5分，共20分） Member(fb_account, name, mobile) PK：fb_account Group(group_code, start_datetime, end_datetime, item, unit_price) PK： group_code Buy_Record(fb_account, group_code, order_datetime, quantity) PK：fb_account+group_code FK：fb_account ref. Member (fb_account) FK：group_code ref. Group 註：查詢結果如非資料表的欄位名，請以別名形式，重新給予有意義的欄位名。 (一)請列出2023/01/01~2023/06/30間有採購「綜合維他命」的成員及採購訊息，包括成員名稱、手機號碼、團號、物品、開團日期時間、結團日期時間、訂購日期、數量、消費金額，結果請依消費金額由多至少排序。 (二)請列出每團採購數量最多的成員及其採購訊息，包括成員名稱、手機號碼、團號、物品、數量、消費金額，結果請依採購數量由少至多排序顯示。 (三)請列出目前尚在採購中的團購團訊息，包括團號、開團日期時間、結團日期時間、物品、目前累計總數量，結果請依開團日期時間由近至遠排序顯示。 (四)請列出2023 一、二兩個月份，參與團購團數 > 1 客戶的訊息，包括客戶名稱、手機號碼、參與的團數。 |
答：
Member資料表：
|
fb_account |
group_code |
order_datetime |
quantity |
|
F001 |
G001 |
2023-01-02 00:00:00.000 |
100 |
|
F001 |
G002 |
2023-02-05 00:00:00.000 |
100 |
|
F001 |
G004 |
2023-04-17 00:00:00.000 |
200 |
|
F002 |
G001 |
2023-01-03 00:00:00.000 |
300 |
|
F003 |
G001 |
2023-01-12 00:00:00.000 |
200 |
|
F003 |
G002 |
2023-02-14 00:00:00.000 |
500 |
|
F003 |
G003 |
2023-03-15 00:00:00.000 |
600 |
|
F003 |
G004 |
2023-04-27 00:00:00.000 |
200 |
|
F004 |
G003 |
2023-03-16 00:00:00.000 |
100 |
|
F004 |
G005 |
2023-07-16 00:00:00.000 |
100 |
Group資料表：
|
group_code |
start_datetime |
end_datetime |
item |
unit_price |
|
G001 |
2023-01-01 00:00:00.000 |
2023-01-12 00:00:00.000 |
綜合維他命 |
200.00 |
|
G002 |
2023-02-05 00:00:00.000 |
2023-02-28 00:00:00.000 |
物品二 |
150.00 |
|
G003 |
2023-03-01 00:00:00.000 |
2023-03-18 00:00:00.000 |
物品三 |
100.00 |
|
G004 |
2023-04-05 00:00:00.000 |
2023-04-28 00:00:00.000 |
綜合維他命 |
300.00 |
|
G005 |
2023-07-01 00:00:00.000 |
2023-07-26 00:00:00.000 |
物品五 |
200.00 |
Buy_Record資料表：
|
fb_account |
group_code |
order_datetime |
quantity |
|
F001 |
G001 |
2023-01-02 00:00:00.000 |
100 |
|
F001 |
G002 |
2023-02-05 00:00:00.000 |
100 |
|
F001 |
G004 |
2023-04-17 00:00:00.000 |
200 |
|
F002 |
G001 |
2023-01-03 00:00:00.000 |
300 |
|
F003 |
G001 |
2023-01-12 00:00:00.000 |
200 |
|
F003 |
G002 |
2023-02-14 00:00:00.000 |
500 |
|
F003 |
G003 |
2023-03-15 00:00:00.000 |
600 |
|
F003 |
G004 |
2023-04-27 00:00:00.000 |
200 |
|
F004 |
G003 |
2023-03-16 00:00:00.000 |
100 |
|
F004 |
G005 |
2023-07-16 00:00:00.000 |
100 |
(一)列出2023/01/01~2023/06/30間有採購「綜合維他命」的成員及採購訊息，包括成員名稱、手機號碼、團號、物品、開團日期時間、結團日期時間、訂購日期、數量、消費金額，結果請依消費金額由多至少排序
Select M.name As 成員名稱, M.mobile As 手機號碼, G.group_code As 團號,
G.item As 物品, G.start_datetime As 開團日期時間,
G.end_datetime As 結團日期時間, BR.order_datetime As 訂購日期,
BR.quantity As 數量, G.unit_price*BR.quantity As 消費金額
From Buy_Record As BR, Member as M, [Group] as G
Where BR.fb_account = M.fb_account And BR.group_code = G.group_code And
order_datetime Between '2023-01-01' And '2023-06-30' And
G.item = '綜合維他命'
Order By 消費金額 Desc
執行結果：
|
成員名稱 |
手機號碼 |
團號 |
物品 |
開團日期時間 |
結團日期時間 |
訂購日期 |
數量 |
消費金額 |
|
王一 |
0912-000-001 |
G004 |
綜合維他命 |
2023-04-05 00:00:00.000 |
2023-04-28 00:00:00.000 |
2023-04-17 00:00:00.000 |
200 |
60000.00 |
|
張二 |
0912-000-002 |
G001 |
綜合維他命 |
2023-01-01 00:00:00.000 |
2023-01-12 00:00:00.000 |
2023-01-03 00:00:00.000 |
300 |
60000.00 |
|
吳三 |
0912-000-003 |
G004 |
綜合維他命 |
2023-04-05 00:00:00.000 |
2023-04-28 00:00:00.000 |
2023-04-27 00:00:00.000 |
200 |
60000.00 |
|
吳三 |
0912-000-003 |
G001 |
綜合維他命 |
2023-01-01 00:00:00.000 |
2023-01-12 00:00:00.000 |
2023-01-12 00:00:00.000 |
200 |
40000.00 |
|
王一 |
0912-000-001 |
G001 |
綜合維他命 |
2023-01-01 00:00:00.000 |
2023-01-12 00:00:00.000 |
2023-01-02 00:00:00.000 |
100 |
20000.00 |
(二)列出每團採購數量最多的成員及其採購訊息，包括成員名稱、手機號碼、團號、物品、數量、消費金額，結果請依採購數量由少至多排序顯示
DROP VIEW IF EXISTS [dbo].[MaxQuantityPerGroup]
GO
Create View MaxQuantityPerGroup As
Select BR.group_code As 團號, MAX(BR.quantity) As 最大數量
From Buy_Record AS BR
Group By BR.group_code
GO
Select M.name As 成員名稱, M.mobile As 手機號碼, G.group_code As 團號,
G.item As 物品, BR.quantity As 數量, G.unit_price*BR.quantity As 消費金額
From Buy_Record As BR, Member as M, [Group] as G, MaxQuantityPerGroup MaxQ
Where BR.fb_account = M.fb_account And BR.group_code = G.group_code And
BR.group_code = MaxQ.團號 AND BR.quantity = MaxQ.最大數量
Order By BR.quantity Asc
執行結果：
|
成員名稱 |
手機號碼 |
團號 |
物品 |
數量 |
消費金額 |
|
陳四 |
0912-000-004 |
G005 |
物品五 |
100 |
20000.00 |
|
王一 |
0912-000-001 |
G004 |
綜合維他命 |
200 |
60000.00 |
|
吳三 |
0912-000-003 |
G004 |
綜合維他命 |
200 |
60000.00 |
|
張二 |
0912-000-002 |
G001 |
綜合維他命 |
300 |
60000.00 |
|
吳三 |
0912-000-003 |
G002 |
物品二 |
500 |
75000.00 |
|
吳三 |
0912-000-003 |
G003 |
物品三 |
600 |
60000.00 |
(三)列出目前尚在採購中的團購團訊息，包括團號、開團日期時間、結團日期時間、物品、目前累計總數量，結果請依開團日期時間由近至遠排序顯示
Select G.group_code As 團號, G.start_datetime As 開團日期時間,
G.end_datetime As 結團日期時間, G.item As 物品,
Sum(BR.quantity) As 目前累計總數量
From Buy_Record As BR, Member as M, [Group] as G
Where BR.fb_account = M.fb_account And BR.group_code = G.group_code And
GETDATE( ) Between G.start_datetime And G.end_datetime
Group By G.group_code, G.start_datetime, G.end_datetime, G.item
Order By 開團日期時間 Desc
執行結果：
|
團號 |
開團日期時間 |
結團日期時間 |
物品 |
目前累計總數量 |
|
G001 |
2023-01-01 00:00:00.000 |
2023-01-12 00:00:00.000 |
綜合維他命 |
600 |
(四)列出2023 一、二兩個月份，參與團購團數 > 1 客戶的訊息，包括客戶名稱、手機號碼、參與的團數
Select M.name As 客戶名稱, M.mobile As 手機號碼,
Count(BR.group_code) As 參與的團數
From Buy_Record As BR, Member as M, [Group] as G
Where BR.fb_account = M.fb_account And BR.group_code = G.group_code And
YEAR(BR.order_datetime) = 2023 And MONTH(BR.order_datetime) IN (1, 2)
Group By M.name, M.mobile
Having Count(BR.group_code) > 1
執行結果：
|
客戶名稱 |
手機號碼 |
參與的團數 |
|
王一 |
0912-000-001 |
2 |
|
吳三 |
0912-000-003 |
2 |
留言列表
國考資訊處理解答 (7)
