Oracle练习

ORACLE第二章课后练习题答案

为《自来水收费系统》开发统计模块相关的功能
1.收费日报单（总）
统计某日的收费，按区域分组汇总，效果如下：

语句：
select (select name from T_AREA where id= areaid ) 区域,
sum(usenum)/1000 “用水量(吨)” ,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy-mm-dd’)=‘2012-05-14’
group by areaid

2.收费日报单（收费员）
统计某收费员某日的收费，按区域分组汇总，效果如下：

语句：
select (select name from T_AREA where id= areaid ) 区域,
sum(usenum)/1000 “用水量(吨)” ,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy-mm-dd’)=‘2012-05-14’
and feeuser=2
group by areaid

3.收费月报表（总）
统计某年某月的收费记录，按区域分组汇总

语句:
select (select name from T_AREA where id= areaid ) 区域,
sum(usenum)/1000 “用水量(吨)” ,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy-mm’)=‘2012-05’
group by areaid

4.收费月报表（收费员）
统计某收费员某年某月的收费记录，按区域分组汇总
语句：
select (select name from T_AREA where id= areaid ) 区域,
sum(usenum)/1000 “用水量(吨)” ,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy-mm’)=‘2012-05’ and feeuser=2
group by areaid

5.收费年报表（分区域统计）
统计某年收费情况，按区域分组汇总，效果如下：

语句：
select (select name from T_AREA where id= areaid ) 区域,
sum(usenum)/1000 “用水量(吨)” ,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy’)=‘2012’
group by areaid

6.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下

语句：
select to_char(feedate,‘mm’) 月份,sum(usenum)/1000 使用吨数,sum(money) 金额
from T_ACCOUNT
where to_char(feedate,‘yyyy’)=‘2013’
GROUP BY to_char(feedate,‘mm’)
ORDER BY to_char(feedate,‘mm’)

7.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下

语句:
select ‘用水量(吨)’ 统计项,
sum (case when to_char(feedate,‘mm’)=‘01’ then usenum else 0 end )/1000 一月,
sum (case when to_char(feedate,‘mm’)=‘02’ then usenum else 0 end )/1000 二月,
sum (case when to_char(feedate,‘mm’)=‘03’ then usenum else 0 end )/1000 三月,
sum (case when to_char(feedate,‘mm’)=‘04’ then usenum else 0 end )/1000 四月,
sum (case when to_char(feedate,‘mm’)=‘05’ then usenum else 0 end )/1000 五月,
sum (case when to_char(feedate,‘mm’)=‘06’ then usenum else 0 end )/1000 六月,
sum (case when to_char(feedate,‘mm’)=‘07’ then usenum else 0 end )/1000 七月,
sum (case when to_char(feedate,‘mm’)=‘08’ then usenum else 0 end )/1000 八月,
sum (case when to_char(feedate,‘mm’)=‘09’ then usenum else 0 end )/1000 九月,
sum (case when to_char(feedate,‘mm’)=‘10’ then usenum else 0 end )/1000 十月,
sum (case when to_char(feedate,‘mm’)=‘11’ then usenum else 0 end )/1000 十一月,
sum (case when to_char(feedate,‘mm’)=‘12’ then usenum else 0 end )/1000 十二月
from T_ACCOUNT
where to_char(feedate,‘yyyy’)=‘2013’
UNION ALL
select ‘金额(元)’ 统计项,
sum (case when to_char(feedate,‘mm’)=‘01’ then money else 0 end ) 一月,
sum (case when to_char(feedate,‘mm’)=‘02’ then money else 0 end ) 二月,
sum (case when to_char(feedate,‘mm’)=‘03’ then money else 0 end ) 三月,
sum (case when to_char(feedate,‘mm’)=‘04’ then money else 0 end ) 四月,
sum (case when to_char(feedate,‘mm’)=‘05’ then money else 0 end ) 五月,
sum (case when to_char(feedate,‘mm’)=‘06’ then money else 0 end ) 六月,
sum (case when to_char(feedate,‘mm’)=‘07’ then money else 0 end ) 七月,
sum (case when to_char(feedate,‘mm’)=‘08’ then money else 0 end ) 八月,
sum (case when to_char(feedate,‘mm’)=‘09’ then money else 0 end ) 九月,
sum (case when to_char(feedate,‘mm’)=‘10’ then money else 0 end ) 十月,
sum (case when to_char(feedate,‘mm’)=‘11’ then money else 0 end ) 十一月,
sum (case when to_char(feedate,‘mm’)=‘12’ then money else 0 end ) 十二月
from T_ACCOUNT
where to_char(feedate,‘yyyy’)=‘2013’

8.统计用水量，收费金额（分类型统计）
根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如果该类型在台账表中无数据也需要列出值为0的记录 ， 效果如下：

语句：
select ow.name,
nvl( round(sum(usenum)/1000),0) “用水量(吨)” , nvl( sum(money),0) 金额
from T_OWNERTYPE ow ,T_ACCOUNT ac
where ow.id=ac.ownertype(+)
group by ow.name

分析：这里所用到的知识点包括左外连接、sum()、分组group by 、round() 和nvl()

9.统计每个区域的业主户数，并列出合计

语句：
select ar.name 区域,count(ow.id) 业主户数
from T_AREA ar ,T_OWNERS ow,T_ADDRESS ad
where ad.id=ow.addressid and ad.areaid=ar.id
group by ar.name
union all
select ‘合计’,count(1) from T_OWNERS

10.统计每个区域的业主户数，如果该区域没有业主户数也要列出0
如图:

语句：
select ar.name 区域,count(owad.id) 业主户数
from T_AREA ar ,
(
select ow.id,ow.name,ad.areaid from T_OWNERS ow,T_ADDRESS ad where ow.addressid=ad.id
)
owad
where ar.id=owad.areaid(+)
group by ar.name