oracle資料庫設計第一次作業

(1)安裝oracle 11g

(2)執行指令碼，建立“家庭成員表”和“家庭開支表”

(3)完成以下業務邏輯：

   A:查詢出每個家庭成員所有開支總和。

      成員ID    成員名     總開支
      
   B:查詢開銷事件為”購物“的最高開支明細。

      家庭成員ID   成員名  開支金額 交易時間

   C:按pageSize=3,pageNo=2的方式，獲取開支明細表的分頁記錄

       家庭成員ID   成員名  開支金額 交易時間

   D:查詢家庭成員為"劉德華"的各個“開銷事件”的最大開銷記錄
        家庭成員ID      成員名       開銷事件      開支金額   交易時間
   E:查詢總開支最多的家庭成員資訊

         家庭成員ID   成員名  開支金額 

   F:查詢近2天的開銷記錄

     家庭成員ID   成員名  開支金額 交易時間

A題：


select f.id 成員ID,f.membername 姓名,sum(s.amount) as 總開支
from family_members f,spend s
where f.id = s.fmid
group by f.id,f.memberName;


總結：要求得每個家庭成員的總開支，而且要求查詢結果集中包含id和姓名，那麼就必須使用兩個表分別是家庭成員表和開支表。首先我們肯定會考慮聚合函式sum，但是我卻沒有想到聚合函式一般都是要和group by聯合使用，於是我用子查詢來實現，雖然實現了但是過程很繁瑣感覺沒有邏輯性。
select 
a.fmid 成員id,
(select membername from family_members where id=a.fmid) 成員名,--查詢出成員名稱
(select sum(amount) from spend where fmid=a.fmid) 總開支 --使用sum函式求出各個成員的總開支
 from
(select distinct fmid from spend) a;--查詢出不同的fmid集合
答案其實就是先在spend表裡面給每條記錄都加上對應的姓名，然後再根據id和姓名來進行group by算出相同id或者相同姓名下的開支總和，進而算出每個家庭成員的開支總和。另外需要注意的是group by中必須包含所有之前select語句中沒有在聚合函式中的所有列。所以在之後的group by語句中你不能僅僅根據id或者name來進行分組。


B題：


select f.id as 成員ID,f.membername as 姓名,s.amount as 開支金額,s.createdtm as 消費時間
from family_members f, spend s
where f.id = s.fmid and s.matter='購物' and s.amount>=(select max(amount) from spend where matter='購物')


總結：這道題一看還是得用兩張表，而且思路就是找出最高紀錄然後用>=符號來求出所有的最高紀錄的明細。上面是一種方法，還可以使用all函式，只不過max函式作用的是屬性，而all函式是作用於結果集的。我還用了老師上課講的一種笨方法：
select * from
spend s where s.amount= --使用where語句查詢所有消費最高紀錄的明細
(
select a.amount
from 
(select * from spend where matter='購物' order by amount desc) a
where rownum=1--先選出最高價格
);
上面這種方法主要是用排序的思想，還用了rownum這個偽列。查詢出的結果集是一樣的。




C題：


select s.fmId as　成員ID，f.memberName as 成員名，s.amount as 開支金額,s.createDtm as 消費時間
from spend s, family_members f
where s.id in (select id from spend where rownum<=3*2) 
and 
s.id not in (select id from spend where rownum<=3*1)
and 
f.id=s.fmid;


總結：這道題就是對rownum進行分組的。


D題:


select f.id as　成員ID,f.membername as 成員名,s.matter as 事件,s.amount as 開支金額,s.createdtm as 消費時間
from family_members f,spend s
where f.id=s.fmid and f.membername='劉德華' and s.amount>=
all(select s.amount from spend s,family_members f where s.fmid=f.id and f.membername='劉德華' )


總結：思路和上上題是一樣的。除了這樣寫，我們還可以通過結合max和group by 來查詢出一樣的結果集。怎麼查呢？我們只需要group by一下然後再用max amount就可以了。如下：
select f.id as　成員ID,f.membername as 成員名,s.matter as 事件,max(s.amount) as 開支金額,s.createdtm as 消費時間
from family_members f,spend s
where f.id=s.fmid and f.membername='劉德華'
group by f.id,f.membername,s.matter,s.createdtm


E題：


select s.fmId as 成員ID，f.memberName as 成員名, sum(s.amount) as 消費金額
from spend s, family_members f
where s.fmid=f.id
group by s.fmid, f.membername
having sum(s.amount)>=all(select sum(amount) from spend group by fmid);




總結：這道題就是對第一題的擴充套件，就是對sum列進行限制求出最大的，但是聚合函式無法和where語句一起使用，所以我們需要使用為此而設計的having語句，having語句的條件就是sum值是三者中最大的。因此需要再次進行select。


F題：


select s.fmId as 成員ID，f.memberName as 成員姓名，s.amount as 消費金額,s.createDtm as 消費時間
from spend s, family_members f
where s.fmid=f.id and sysdate-s.createDtm<=2;


總結：關於系統時間的操作和運算要熟悉。