row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
from bp_j_stat_ntz n
where n.org_code='''||v_complany||'''
and to_char(n.data_date,''yyyy-mm'') = '''||to_char(d_date,'yyyy-mm') ||'''
) t where t.rn=1) t'||n_i||'
on i.item_code_prefix=t'||n_i||'.item_code_prefix
';
end if;
v_fields := v_fields || 't' || n_i || '.data_value as "'||v_year||'",';
n_i := n_i+1;
d_date :=add_months(d_date,12);
end loop;
v_sql_t := 'select i.item_code_prefix,
lpad('' '',2 * level - 2) || i.item_name as item_name,
u.unit_name,
'||v_fields||'
from bp_c_stat_item i
left join bp_c_measure_unit u
on i.unit_id=u.unit_id
';
v_sql := v_sql_t || v_sql || '
where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where ||
'start with i.item_code_prefix = '''||v_itemCode || '''
connect by prior i.item_code_prefix=i.parent_item_code
order SIBLINGS BY i.order_by asc';
end if;
--打开游标
open ds for v_sql;
end pro_tj_getDate_normal_year;
这个sql实现了根据动态日期展示指标数据，很实用哦。如果大家有其他更好的办法可以一起讨论。