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;


总结：关于系统时间的操作和运算要熟悉。