1,统计某日的收费,按区域分组汇总
已知表 t_account (收费台账表)
已知表 t_area (区域表)
select (select name from t_area where ac.areaid = t_area.id ) 区域 ,
sum(ac.money) 收费, sum(ac.usenum)/1000 用水量吨
from t_account ac
where to_char(feedate,'yyyy-mm-dd') = '2012-08-14' -- 某日
group by ac.areaid -- 按区域分组
2,统计某收费员某日的收费,按区域分组汇总
select (select name from t_area where ac.areaid = t_area.id ) 区域 ,
sum(ac.money) 收费, sum(ac.usenum)/1000 用水量吨
from t_account ac
where to_char(feedate,'yyyy-mm-dd') = '2012-08-14' -- 某日
and ac.feeuser = 2 -- 某收费员
group by ac.areaid -- 按区域分组
3,统计某年某月的收费记录,按区域分组汇总
select (select name from t_area where ac.areaid = t_area.id ) 区域 ,
sum(ac.money) 收费, sum(ac.usenum)/1000 用水量吨
from t_account ac
where to_char(feedate,'yyyy-mm') = '2012-08' -- 某年某月
group by ac.areaid -- 按区域分组
4,统计某收费员某年某月的收费记录,按区域分组汇总
select (select name from t_area where ac.areaid = t_area.id ) 区域 ,
sum(ac.money) 收费, sum(ac.usenum)/1000 用水量吨
from t_account ac
where to_char(feedate,'yyyy-mm') = '2012-08' -- 某年某月
and ac.feeuser = 2 -- 某收费员
group by ac.areaid -- 按区域分组
5,统计某年收费情况,按区域分组汇总
select (select name from t_area where ac.areaid = t_area.id ) 区域 ,
sum(ac.money) 收费, sum(ac.usenum)/1000 用水量吨
from t_account ac
where to_char(feedate,'yyyy') = '2012' -- 某年
group by ac.areaid -- 按区域分组
6,统计某年收费情况,按月份分组汇总
select to_char(feedate,'mm') 月份 ,sum(money) 金额, sum(usenum)/1000 使用吨数 from t_account
where to_char(feedate,'yyyy') = 2012 -- 某年
group by to_char(feedate,'mm') -- 根据月份分组
7, 统计用水量,收费金额(分类型统计)
根据业主类型分别统计每种居民的用水量(整数,四舍五入)及收费金额
已知表:t_ownertype(业务类型表)
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
8,统计每个区域的业主户数,并列出合计
已知表 t_owners(业主)
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
9,统计每个区域的业主户数,如果该区域没有业主户数也要列出0
select ar.name 区域,count(s.id) 业主户数
from t_area ar ,
(select ow.id,ow.name,ad.areaid from t_owners ow,t_address ad where ow.addressid=ad.id) s
where ar.id=s.areaid(+)
group by ar.name
更多推荐
自来水收费相关的sql语句题
发布评论