Oracle水厂收费系统练习拓展

title: Oracle水厂收费系统练习拓展
date: 2018-1-28 22:05:23
categories: Oracle
tags: Oracle

---

xl_echo编辑整理，欢迎转载，转载请声明文章来源。更多案例、资料请联系QQ：1280023003

Oracle水厂收费系统练习拓展

-- 统计某日的收费，按区域分组汇总
select (select name from t_area where id = t.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account t 
where to_char(t.feedate, 'yyyy-mm-dd')='2012-05-14' group by t.areaid;
-- 注意：以上语句中areaid，如果在没有分组的情况下，不能直接跟sum函数拼接查询


-- 统计某收费员某日的收费，按区域分组汇总
select (select name from t_area where id = ac.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.feedate, 'yyyy-mm-dd') = '2012-05-14' and ac.feeuserid = 2 group by ac.areaid;

-- 统计某年某月的收费记录，按区域分组汇总
select (select name from t_area where id = ac.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.feedate, 'yyyy-mm') = '2012-05' group by ac.areaid;

select (select name from t_area where id = ac.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.meterdate, 'yyyy-mm-dd') = '2018-01-26' group by ac.areaid;
select to_char(ac.meterdate, 'yyyy-mm-dd') from t_account ac

-- 统计某收费员某年某月的收费记录，按区域分组汇总
select (select name from t_area where id = ac.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.feedate, 'yyyy-mm') = '2012-05' and ac.feeuserid = 2 group by ac.areaid;

-- 统计某年收费情况，按区域分组汇总
select (select name from t_area where id = ac.areaid) 区域, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.feedate, 'yyyy') = '2012' group by ac.areaid;

-- 统计某年收费情况，按月份分组汇总
select ac.month 月份, sum(usernum)/1000 用水量, sum(money) 金额 
from t_account ac 
where to_char(ac.feedate, 'yyyy') = '2013' group by ac.month order by ac.month;

-- 统计某年收费情况，按月份分组汇总
select  '用水量'统计项,
sum(case when ac.month = '01' then usernum end) 一月,
sum(case when ac.month = '02' then usernum end) 二月,
sum(case when ac.month = '03' then usernum end) 三月,
sum(case when ac.month = '04' then usernum end) 四月,
sum(case when ac.month = '05' then usernum end) 五月,
sum(case when ac.month = '06' then usernum end) 六月,
sum(case when ac.month = '07' then usernum end) 七月,
sum(case when ac.month = '08' then usernum end) 八月,
sum(case when ac.month = '09' then usernum end) 九月,
sum(case when ac.month = '10' then usernum end) 十月,
sum(case when ac.month = '11' then usernum end) 十一月,
sum(case when ac.month = '12' then usernum end) 十二月
from t_account ac where to_char(ac.feedate, 'yyyy') = '2013'
union all
select  '金额'统计项,
sum(case when ac.month = '01' then money end) 一月,
sum(case when ac.month = '02' then money end) 二月,
sum(case when ac.month = '03' then money end) 三月,
sum(case when ac.month = '04' then money end) 四月,
sum(case when ac.month = '05' then money end) 五月,
sum(case when ac.month = '06' then money end) 六月,
sum(case when ac.month = '07' then money end) 七月,
sum(case when ac.month = '08' then money end) 八月,
sum(case when ac.month = '09' then money end) 九月,
sum(case when ac.month = '10' then money end) 十月,
sum(case when ac.month = '11' then money end) 十一月,
sum(case when ac.month = '12' then money end) 十二月
from t_account ac where to_char(ac.feedate, 'yyyy') = '2013';

-- 根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如果该类型在台账表中无数据也需要列出值为0的记录
select ow.name, nvl(round(sum(usernum)/1000), 0) 用水量, nvl(sum(money),0) 金额 
from t_ownertype ow, t_account ac 
where ow.id=ac.ownertypeid(+)
group by ow.name;

-- 统计每个区域的业主户数，并列出合计
select ar.name 区域, count(ow.id) 业主户数
from t_area ar, t_owners ow, t_address ad
where ar.id=ad.areaid and ow.addressid=ad.id
group by ar.name
union all
select '统计', count(*) from t_owners;

-- 统计每个区域的业主户数，如果该区域没有业主户数也要列出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