update cust_info t2 set t2.cust_type=(SELECT t1.cust_type from (SELECT a.cust_id ,b.cn*0.2 二十,b.cn*0.8 八十,b.cn,
case when rn <=b.cn*.2 then '核心' when rn > b.cn*.2 and rn <= b.cn*.8 then '战略' else '普通' end cust_type
FROM
(SELECT a.cust_id,rank() over (ORDER BY a.money) rn from cust_dep_info a WHERE a.mounth='你的日期') a
,(SELECT COUNT(*) cn FROM cust_dep_info a WHERE a.mounth='你的日期') b
) t1 where t1.cust_id=t2.cust_id);如果不想更新,只想看看的话,只要中间的t1就行了
还有如果有并列的情况怎么处理,你再自己看看吧
修改自上个问题2楼,还有你的条件应该是在全部用户中 每个客户的money占总money的比例 降序序排列以后,取人数总数的百分比
RetMemo out varchar2)
is
v_cust_type varchar2(20);
cust_num number;
v_count number;
cursor c_find is
select cust_id, rank() over(order by cust_money desc) as rnk
from (select cust_id, sum(money) cust_money
from cust_dep_info
where mounth = in_date
group by cust_id);
cc c_find%rowtype;
begin
RetMemo := '';
v_count := 0;
select count(distinct cust_id)
into cust_num
from cust_dep_info
where mounth = in_date;
open c_find;
loop
fetch c_find
into cc;
exit when c_find%notfound;
if cc.rnk >= 0 and cc.rnk <= round(cust_num * 0.2) then
v_cust_type := '核心客户';
elsif cc.rnk > round(cust_num * 0.2) and
cc.rnk <= round(cust_num * 0.8) then
v_cust_type := '战略客户';
elsif cc.rnk > round(cust_num * 0.8) and cc.rnk <= cust_num then
v_cust_type := '普通客户';
end if;
update cust_info
set cust_type = v_cust_type
where cust_id = cc.cust_id;
commit;
v_count := v_count + 1;
end loop;
close c_find;
RetMemo := RetMemo || '成功更新了' || v_count || '条数据';
exception
when others then
begin
rollback;
RetMemo := sqlerrm;
return;
end;
end;
from cust_dep_info
where mounth = in_date) 你把游标改成这样就符合了吧