update t set cm_name=a.cm_name,cus_mobile=a.cus_mobile from (select rn=row_number() over (partition by cus_id order by getdate()),* from t where cm_name is not null) a where a.cus_id=t.cus_id and rn='1'
错了,手机号码还是原来的. update t set cm_name=a.cm_name from (select rn=row_number() over (partition by cus_id order by getdate()),* from t where cm_name is not null) a where a.cus_id=t.cus_id and rn='1'
UPDATE a SET CM_NAME = (SELECT TOP 1 CM_NAME FROM TABLE WHERE CUS_ID=a.CUS_ID AND CM_NAME IS NOT NULL) FROM [TABLE] a
;with cte as( select row_number() over (partition by CUS_ID order by getdate()) m,* from T ) select CUS_ID, (select top 1 CM_NAME from cte where CUS_ID=a.CUS_ID and isnull(CM_NAME,'')!='' order by m )CM_NAME ,CreateUid ,CUS_MOBILEfrom cte a order by CUS_ID
;with cte as( select row_number() over (partition by CUS_ID order by getdate()) m,* from #a ) select CUS_ID, (select top 1 CM_NAME from cte where CUS_ID=a.CUS_ID and isnull(CM_NAME,'')!='' order by m )CM_NAME ,CUS_MOBILE from cte a order by CUS_ID ----查询结果 (9 行受影响) CUS_ID CM_NAME CUS_MOBILE ----------- -------------------------------------------------- ----------- 101 李强 1311234567 101 李强 1321234567 101 李强 1331234567 102 李四 1891234567 102 李四 1381234567 103 王五 1380000000 103 王五 1361234567 104 郭晓 1341234561 104 郭晓 1321456123
create table 客户经理服务对照表 (CUS_ID int,CM_NAME varchar(10),CUS_MOBILE varchar(20))insert into 客户经理服务对照表 select 101,'李强','1311234567' union all select 101,'周倩','1321234567' union all select 101,'张三','1331234567' union all select 102,NULL,'1891234567' union all select 102,'李四','1381234567' union all select 103,'王五','1380000000' union all select 103,NULL,'1361234567' union all select 104,'郭晓','1341234561' union all select 104,'张雨','1321456123' update t set t.CM_NAME=(select top 1 u.CM_NAME from 客户经理服务对照表 u where u.CUS_ID=t.CUS_ID and u.CM_NAME is not null) from 客户经理服务对照表 t select * from 客户经理服务对照表/* CUS_ID CM_NAME CUS_MOBILE ----------- ---------- -------------------- 101 李强 1311234567 101 李强 1321234567 101 李强 1331234567 102 李四 1891234567 102 李四 1381234567 103 王五 1380000000 103 王五 1361234567 104 郭晓 1341234561 104 郭晓 1321456123(9 row(s) affected) */
(select rn=row_number() over (partition by cus_id order by getdate()),* from t where cm_name is not null) a
where a.cus_id=t.cus_id and rn='1'
update t set cm_name=a.cm_name from
(select rn=row_number() over (partition by cus_id order by getdate()),* from t where cm_name is not null) a
where a.cus_id=t.cus_id and rn='1'
FROM [TABLE] a
;with cte as(
select row_number() over (partition by CUS_ID order by getdate()) m,* from T
)
select CUS_ID, (select top 1 CM_NAME from cte where CUS_ID=a.CUS_ID and isnull(CM_NAME,'')!='' order by m )CM_NAME
,CreateUid ,CUS_MOBILEfrom cte a order by CUS_ID
select row_number() over (partition by CUS_ID order by getdate()) m,* from #a
)
select CUS_ID, (select top 1 CM_NAME from cte where CUS_ID=a.CUS_ID and isnull(CM_NAME,'')!='' order by m )CM_NAME
,CUS_MOBILE from cte a order by CUS_ID
----查询结果
(9 行受影响)
CUS_ID CM_NAME CUS_MOBILE
----------- -------------------------------------------------- -----------
101 李强 1311234567
101 李强 1321234567
101 李强 1331234567
102 李四 1891234567
102 李四 1381234567
103 王五 1380000000
103 王五 1361234567
104 郭晓 1341234561
104 郭晓 1321456123
create table 客户经理服务对照表
(CUS_ID int,CM_NAME varchar(10),CUS_MOBILE varchar(20))insert into 客户经理服务对照表
select 101,'李强','1311234567' union all
select 101,'周倩','1321234567' union all
select 101,'张三','1331234567' union all
select 102,NULL,'1891234567' union all
select 102,'李四','1381234567' union all
select 103,'王五','1380000000' union all
select 103,NULL,'1361234567' union all
select 104,'郭晓','1341234561' union all
select 104,'张雨','1321456123'
update t
set t.CM_NAME=(select top 1 u.CM_NAME
from 客户经理服务对照表 u
where u.CUS_ID=t.CUS_ID and u.CM_NAME is not null)
from 客户经理服务对照表 t
select * from 客户经理服务对照表/*
CUS_ID CM_NAME CUS_MOBILE
----------- ---------- --------------------
101 李强 1311234567
101 李强 1321234567
101 李强 1331234567
102 李四 1891234567
102 李四 1381234567
103 王五 1380000000
103 王五 1361234567
104 郭晓 1341234561
104 郭晓 1321456123(9 row(s) affected)
*/