你好,表T_Hang (记录每个CUS_ID在T_3的行数),这里看到101已经是150行了,所以需要向T_3中插入350行试试这个:;with t as ( select ts.*, th.T_NUM, ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum from T_Source ts inner join T_Hand th on ts.cus_id= th.cus_id where th.T_NUM < 500 ) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME) select CUS_ID,CUS_MOBILE,CM_NAME from t where rownum <= 500 - T_NUM
你好,表T_Hang (记录每个CUS_ID在T_3的行数),这里看到101已经是150行了,所以需要向T_3中插入350行试试这个:;with t as ( select ts.*, th.T_NUM, ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum from T_Source ts inner join T_Hand th on ts.cus_id= th.cus_id where th.T_NUM < 500 ) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME) select CUS_ID,CUS_MOBILE,CM_NAME from t where rownum <= 500 - T_NUM 大神就是大神, 你这么早就起来提问了。。早起的鸟儿!
你好,表T_Hang (记录每个CUS_ID在T_3的行数),这里看到101已经是150行了,所以需要向T_3中插入350行试试这个:;with t as ( select ts.*, th.T_NUM, ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum from T_Source ts inner join T_Hand th on ts.cus_id= th.cus_id where th.T_NUM < 500 ) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME) select CUS_ID,CUS_MOBILE,CM_NAME from t where rownum <= 500 - T_NUM 大神就是大神, 你这么早就起来提问了。。早起的鸟儿!
insert into T_3
select ts.* from T_Source ts
inner join (select * from T_Hang where T_NUM <500) th
on ts.CUS_ID = th.CUS_ID
as
(
select ts.*, th.T_NUM,
ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum
from T_Source ts
inner join T_Hand th
on ts.cus_id= th.cus_id
where th.T_NUM < 500
) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME)
select CUS_ID,CUS_MOBILE,CM_NAME
from t
where rownum <= 500 - T_NUM
as
(
select ts.*, th.T_NUM,
ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum
from T_Source ts
inner join T_Hand th
on ts.cus_id= th.cus_id
where th.T_NUM < 500
) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME)
select CUS_ID,CUS_MOBILE,CM_NAME
from t
where rownum <= 500 - T_NUM
大神就是大神,
你这么早就起来提问了。。早起的鸟儿!
as
(
select ts.*, th.T_NUM,
ROW_NUMBER() over(PARTITION by CUS_ID order by getdate()) as rownum
from T_Source ts
inner join T_Hand th
on ts.cus_id= th.cus_id
where th.T_NUM < 500
) insert into T_3(CUS_ID,CUS_MOBILE,CM_NAME)
select CUS_ID,CUS_MOBILE,CM_NAME
from t
where rownum <= 500 - T_NUM
大神就是大神,
你这么早就起来提问了。。早起的鸟儿!
这里记录客户经理现在的服务对象,有客户ID,手机号码等,为方便举例只列举3个字段客户编码, 客户手机号, 客户服务经理名字
CUS_ID CUS_MOBILE CM_NAME
-----------------------------------------------------------------
101, 1301234567, 陈琳
102, 1311234456, 吴非
102, 1321234567, 吴非
103, 1331234564, 谢可2. 客户经理目标表,(T_Target):
这里记录客户经理今年的目标服务对象是多少,显示是多少(也就是T_1表中的count(CUS_ID)GROUP BY CM_NAME的计算结果),还差多少,客户服务经理名字, 目标, 现在多少个,还差几个
CM_NAME T_Target T_Existing T_Gap
--------------------------------------------------
陈琳, 2, 1, 1
吴非, 4, 2, 2
谢可, 2, 1, 13. 源数据表,(T_Source):
这里存放客户的数据客户编码, 客户手机号
CUS_ID CUS_MOBILE
----------------------------------
201, 1351234567
201, 1361234456
202, 1371234567
203, 1381234564
203, 1391234564
-------得到的结果应该是更新表T_1
客户编码, 客户手机号, 客户服务经理名字
[b]CUS_ID CUS_MOBILE CM_NAME
-----------------------------------------------------------------
101, 1301234567, 陈琳
102, 1311234456, 吴非
102, 1321234567, 吴非
103, 1331234564, 谢可
201, 1351234567, 陈琳
201, 1361234456, 陈琳
203, 1381234564, 吴非
203, 1391234564, 吴非
202, 1371234567, 谢可疑问:
1. 这里大家会发现陈琳的目标是2个,但是结果表却是3个陈琳的名字出现,这里是因为一个客户编码只能被一个人服务,所以有个优先原则是:一个客户编码必须对应一个客户经理。
2. 谢可的目标是2,为什么不选择两条203的记录插入呢,是因为约束为目标数值优先原则,所以插入了一条202