SELECT
mon,
line_code,
user_no,
line_power,
pub_users,
pub_sigles,
pub_sale,
pub_sigle_power,
priv_users,
priv_sale,
write_sect_no,
business_place_code,
line_dept_code,
line_type,
line_code_up,
line_volt_level,
sanxiang_users,
sanxiang_sale,
line_lose
FROM (
select
max(mon) mon,
max(line_code) line_code,
max(user_no) user_no,
sum(decode(user_type1,50,line_power,0)) line_power,
sum(pub_users) pub_users,
sum(pub_sigles) pub_sigles,
sum(pub_sale) pub_sale,
sum(pub_sigle_power) pub_sigle_power,
sum(priv_users) priv_users,
sum(priv_sale) priv_sale,
max(write_sect_no) write_sect_no,
max(business_place_code) business_place_code,
max(line_dept_code) line_dept_code,
max(line_type) line_type,
max(line_code_up) line_code_up,
max(line_volt_level) line_volt_level,
nvl((sum(pub_users) - sum(pub_sigles)),0) sanxiang_users,
nvl((sum(pub_sale) - sum(pub_sigle_power)),0) sanxiang_sale,
decode(sum(decode(user_type1,50,line_power,0)),0,0,
round((sum(decode(user_type1,50,line_power,0)) - sum(pub_sale) -
sum(priv_sale))/sum(decode(user_type1,50,line_power,0))*100,2)) line_lose
from line_powers
where user_type1 in ('50','60')
group by line_code)
WHERE LENGTH(LINE_CODE) = '6'
|
