select t.id_ cusId,t.building_no_ buildingNO,t.house_no_ houseNO,t.room_no_ roomNO,
tb.name_ areaName,
tc.year_ chargeYear,
case
when t.AREA_ID_ is null then
'all'
when t.AREA_ID_ is not null and t.BUILDING_NO_ is null then
'all'
when t.AREA_ID_ is not null and
(t.HOUSE_NO_ is null or t.ROOM_NO_ is null) then
'part'
else
t.CUS_NAME_
end cusName,
tc.is_owe_ isOwe,
tt.name_ cusTypeName,
t.tel_ cusTel,
sum(tc.heating_area_) heatingArea,
sum(nvl(cc.real_income_money_,0)) realIncomeMoney,
sum(nvl(decode(t.cus_type_id_,1,byp.JM_PRICE_,2,byp.DS_PRICE_,3,BYP.GJ_PRICE_)*tc.heating_area_,0)) deserveMoney,
nvl(sum(case when tc.IS_OWE_='1' then tc.OWE_MONEY_ end), 0) oweMoney
from t_customer_infos t
left join t_cus_year_status tc on t.id_ = tc.cus_id_
left join t_base_areas tb on t.area_id_ = tb.id_
left join t_base_customer_types tt on t.cus_type_id_ = tt.id_
left join (
select tch.cus_id_,tch.year_,sum(tch.real_income_money_) as real_income_money_
from t_cus_charges tch group by tch.year_,tch.cus_id_
) cc on tc.year_ = cc.year_ and t.id_ = cc.cus_id_
left join t_base_year_price byp on byp.year_ = tc.year_ and byp.status_ = '1'
where t.STATUS_='1' and t.heating_area_<>0
GROUP BY GROUPING SETS((tc.year_),(tc.year_,t.area_id_,tb.name_),(tc.year_,t.area_id_,tb.name_,t.building_no_),
(tc.year_,t.area_id_,tb.name_,t.building_no_,t.house_no_),(tc.year_,t.area_id_,tb.name_,t.building_no_,t.house_no_,t.room_no_,t.cus_name_,tt.name_,t.tel_,tc.is_owe_,t.id_))
order by t.area_id_
其中的‘part’和‘all’，分别表示小计和总计，可以根据具体的需要修改；grouping sets是依次按照括号里面的内容进行分组。
