已开启行式引擎
分页sql如下 SELECT * FROM (SELECT * FROM ( select burks_id, burks_name, piv_hyk_no, psl_pro_id, psl_pro_name, zgg, zsccj, dl_name, zl_name, xl_name, month1, month2, month3, month4, month5, month6, month7, month8, month9, month10, month11, month12,'${p_month}' p_month, month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12 sumallmonth, case when used_days = 0 then 0 else 30/used_days end monthusedqty, case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2)) allusedqty, gk_flag, (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) dot, case when maxpslmonth = '${p_month}' then '是' else '否' end isbuy, case when gk_flag = '是' then '公卡' when gk_flag = '否' then case when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) >=0 and maxpslmonth = '${p_month}' then '优' when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) >=0 and maxpslmonth != '${p_month}' then '高' when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) <0 and maxpslmonth = '${p_month}' then '中' when (month1+month2+month3+month4+month5+month6+month7+month8+month9+month10+month11+month12) - (case when used_days = 0 then 0 else 30/used_days end * to_number(right('${p_month}',2))) <0 and maxpslmonth != '${p_month}' then '低' end end hybq, to_number(right(left(trunc(sysdate()),7),2))-to_number(right(maxmonth,2)) deltmonth from (select p.burks_id,p.burks_name,p.piv_hyk_no,p.psl_pro_id,p.psl_pro_name,p.zgg,p.zsccj,p.dl_name,p.dl_id,p.zl_name,p.zl_id,p.xl_name,p.xl_id,p.gk_flag,nvl(p.used_days,0) used_days,max(p.last_buy_month) maxmonth,max(p.psl_month) maxpslmonth, sum(case when right(p.psl_month,2) = '01' then p.psl_qty else 0 end) month1, sum(case when right(p.psl_month,2) = '02' then p.psl_qty else 0 end) month2, sum(case when right(p.psl_month,2) = '03' then p.psl_qty else 0 end) month3, sum(case when right(p.psl_month,2) = '04' then p.psl_qty else 0 end) month4, sum(case when right(p.psl_month,2) = '05' then p.psl_qty else 0 end) month5, sum(case when right(p.psl_month,2) = '06' then p.psl_qty else 0 end) month6, sum(case when right(p.psl_month,2) = '07' then p.psl_qty else 0 end) month7, sum(case when right(p.psl_month,2) = '08' then p.psl_qty else 0 end) month8, sum(case when right(p.psl_month,2) = '09' then p.psl_qty else 0 end) month9, sum(case when right(p.psl_month,2) = '10' then p.psl_qty else 0 end) month10, sum(case when right(p.psl_month,2) = '11' then p.psl_qty else 0 end) month11, sum(case when right(p.psl_month,2) = '12' then p.psl_qty else 0 end) month12 from dm_rh.dm_hy_sku_dot_sale p group by p.burks_id,p.burks_name,p.piv_hyk_no,p.psl_pro_id,p.psl_pro_name,p.zgg,p.zsccj,p.dl_name,p.zl_name,p.xl_name,p.gk_flag,p.used_days,p.dl_id,p.zl_id,p.xl_id) a1 order by (burks_id,burks_name,piv_hyk_no,psl_pro_id) ASC limit ${fr_pagesize*fr_pagenumber}) AS e1 ORDER BY (e1.burks_id,e1.burks_name,e1.piv_hyk_no,e1.psl_pro_id) DESC limit ${if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)}) AS e2 ORDER BY (e2.burks_id,e2.burks_name,e2.piv_hyk_no,e2.psl_pro_id) ASC报错如下
|
1min目标场景问卷
立即参与
