p_distance_h number,
p_distance_x number,
p_distance_r number,
p_distance_m number,
cur_out out sys_refcursor) is
v_sql varchar2(1000);
v_where varchar2(500);
begin
execute immediate 'truncate table JL_SITEDIFF_detail_now';
v_sql := 'insert into JL_SITEDIFF_detail_now select * ';
v_where := 'from JL_SITEDIFF_DETAIL where first_result=to_date(' || '''' ||
p_starttime || '''' || ',''yyyy-mm-dd'') and (1=2 ';
if p_distance_h > 0 then
v_where := v_where || 'or (sitetype=''H'' and distance<' ||
p_distance_h || ') ';
end if;
if p_distance_x > 0 then
v_where := v_where || 'or (sitetype=''X'' and distance<' ||
p_distance_x || ') ';
end if;
if p_distance_r > 0 then
v_where := v_where || 'or (sitetype=''R'' and distance<' ||
p_distance_h || ') ';
end if;
if p_distance_m > 0 then
v_where := v_where || 'or (sitetype=''M'' and distance<' ||
p_distance_m || ') ';
end if;
v_sql := v_sql || v_where || ')';
--执行汇总
execute immediate v_sql;
commit;
open cur_out for
select distinct order_id,
first_result,
company,
sum(case
when sitetype = 'H' then
1
else
0
end) h,
sum(case
when sitetype = 'R' then
1
else
0
end) r,
sum(case
when sitetype = 'X' then
1
else
0
end) x
,
sum(case
when sitetype = 'M' then
1
else
0
end) m,
count(*) as sum
from jl_sitediff_detail_now
group by first_result, order_id, company
union all
select 10,
first_result,
'吉林省',
sum(case
when sitetype = 'H' then
1
else
0
end) h,
sum(case
when sitetype = 'R' then
1
else
0
end) r,
sum(case
when sitetype = 'X' then
1
else
0
end) x,
sum(case
when sitetype = 'M' then
1
else
0
end) m,
count(*) as sum
from jl_sitediff_detail_now
group by first_result
order by order_id;
--执行去重
--select distinct
--case when sitecode1>=sitecode2 then sitecode2 else sitecode1 end sitecode1,
--case when sitecode1>=sitecode2 then sitecode1 else sitecode2 end sitecode2
-- from JL_SITEDIFF_DETAIL_NOW t
-- where exists(select 1 from JL_SITEDIFF_DETAIL_NOW where sitecode1 =t.sitecode2 and sitecode2=t.sitecode1)
--union all
--select * from JL_SITEDIFF_DETAIL_NOW t where not exists (select 1 from JL_SITEDIFF_DETAIL_NOW t where sitecode1=t.sitecode2 and sitecode2=t.sitecode1)
end sp_report_sitediffdistance;
注释部分是想要嵌入到存储过程中的。。
整个功能是这样未注释的代码指的是根据查询条件显示计数
注释部分的意思是，去除重复数据和交叉相等的数据，只留一个，
跪求各位大神。。
在线等。
