sql语句实现统计

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是依次按照括号里面的内容进行分组。