select * from tfp_cpdm where cpmc like '%国通信托·方兴1070号重庆市黔江区债权投资集合资金信托计划第二期%'
select * from tfp_xtjh where id=4311
var rs=LB_sqlResultSet("SELECT b.roleid RYJS,replace(wm_concat(c.id),',',';') RYXX FROM lbmember b,tryxx c where c.zhzt=0 and c.dyyh=b.userid AND (b.roleid in (1,16) or (b.roleid=3 and b.userid=9) OR UPPER(c.dyry)=?) group by roleid",[rybh]);
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
FROM lbmember b, tryxx c
where c.zhzt = 0
and c.dyyh = b.userid
AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
UPPER(c.dyry) = 'XINGWENJIA')
group by roleid
select chr(39)||123||chr(39) from dual;
select chr(39)||UPPER(htrybh)||chr(39) from tfp_xtjh t,tfp_cpdm cp where t.id=cp.xtjh and cp.id=36044
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
FROM lbmember b, tryxx c
where c.zhzt = 0
and c.dyyh = b.userid
AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
UPPER(c.dyry) = 'CHENGXI')
group by roleid [ 1 :"'CHENGXI'" ]
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
FROM lbmember b, tryxx c
where c.zhzt = 0
and c.dyyh = b.userid
AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
UPPER(c.dyry) = 'XINGWENJIA')
group by roleid
SELECT b.roleid RYJS, replace(wm_concat(c.id), ',', ';') RYXX
FROM lbmember b, tryxx c
where c.zhzt = 0
and c.dyyh = b.userid
AND (b.roleid in (1, 16) or (b.roleid = 3 and b.userid = 9) OR
UPPER(c.dyry) = 'XINGWENJIA')
group by roleid
select khjl from tkhxx where id=59272
select *from tryxx where id=316
select ORGID from tryxx where ryxm like '%陈伟%'
select PROVINCE,ZJLB,fn_decrypt(zjbh),t.* from tkhxx t where khxm like '%吴媛%' 120000 --59272
--吴媛
select *from lborganization where id=103
select * from lborganization b where province=to_char(120000)
-----------------------------------------------------------------------------
insert into tempdata(n1,n2,c1) --n2 0:非全国分配 n3 对应tryxx.id
select id,0,substr(fn_decrypt(ZJBH),1,2)||'0000' from tkhxx a where a.khzt in(2,4) and a.sfdxkh=1;
-----------------------------------------------------------------------------------------------
select * from tempdata WHERE N1=59272
TRUNCATE TABLE tempdata
-------------------------------------------------------------------------------------
SELECT * FROM tempdata A
where not exists(select 1 from lborganization b,tryxx c select * from tryxx r where r.orgid=23
where
-- 42000=42000
-- a.c1=to_char(b.province)
a.c1=to_char(b.province)
and b.orgtype=31 and b.province is NOT null ---天津分中心没有理财经理
and b.id=c.orgid
and c.zhzt=0 select *from lborganization where orgtype=31
and c.ryfl in(1,3)) AND a.N1=59272;
-------------------------------------------------------------------------------
SELECT * FROM tempdata a
where exists(select 1 from lborganization b,tryxx c
where
--42000=42000
a.c1=to_char(b.province)
and b.orgtype=31 and b.province is NOT null
and b.id=c.orgid
and c.zhzt=0
and c.ryfl in(1,3))
AND a.N1=59272;
-------------------------------------------------------------------------------------
SELECT * FROM tempdata a
where exists(select 1 from lborganization b,tryxx c
where
-- 42000=42000
-- a.c1=to_char(b.province)
a.c1=to_char(b.province)
and b.orgtype=31 and b.province is NOT null
and b.id=c.orgid
and c.zhzt=0
and c.ryfl in(1,3)) AND a.N1=59272;
------------------------------------------------------------------------------------
update tempdata a set a.n2=1
where not exists(select 1 from lborganization b,tryxx c
where a.c1=to_char(b.province)
and b.orgtype=31 and b.province is not null
and b.id=c.orgid
and c.zhzt=0
and c.ryfl in(1,3));
COMMIT;
--------------select * from tempdata WHERE N1=59272
select * from tempdata_bb
select * from tempdata_cc
select * from tempdata WHERE N1=59272 select rownum from tryxx where rownum<2
select mod(1,2) from dual;
select * from tempdata where N1=59272
--- tempdata N1: 客户id N2:渠道客户全国分配 C1:省份
----------------------------------------------- tempdata_bb : N1:客户id N2:行号
-------------- tempdate_cc : N1:理财经理id N2:行号-1
--无省直辖市的全国分配
execute immediate 'truncate table tempdata_bb';
execute immediate 'truncate table tempdata_cc';
insert into tempdata_bb(n1,n2)
select n1,rownum from tempdata a where a.n2=1;
--取出待分配的人员
insert into tempdata_cc(n1,n2) -- select *from tempdata_bb WHERE N1=59272
select a.id,rownum-1 select * from tempdata_cc
from tryxx a,lborganization b select count(*) from tempdata_cc
where a.orgid=b.id
and b.orgtype=31
and a.ryfl in(1,3)
and a.zhzt=0;
--平均分配
select count(1) into v_count from tempdata_cc;
update tempdata_bb a set a.n3=(select b.n1 from tempdata_cc b where mod(a.n2,v_count)=b.n2);
--把分配结果更新回主表tempdata
update tempdata a set a.n3=(select b.n3 from tempdata_bb b where a.n1=b.n1)
where exists(select 1 from tempdata_bb b where a.n1=b.n1);
--最后落地所有渠道客户的分配数据
insert into tlskhfpb(id,fppc,fprq,ryxx,khh,khfl,fplb,SFFP)
select a.n1,v_fppc,v_rq,a.n3,a.n1,0,a.n2,2 from tempdata a;
commit;
流失客户分配
最新推荐文章于 2020-05-20 17:25:08 发布