34,838
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([发送时间] Date,[SMS_SERIALS_ID] int)
Insert #T
select '2017-01-01',-1 union all
select '2017-01-01',-1 union all
select '2017-01-02',-1 union all
select '2017-01-02',-1 union all
select '2017-01-03',-1
Go
--测试数据结束
;WITH cte AS (
SELECT 发送时间 ,
COUNT(1) AS [count] ,
ROW_NUMBER() OVER ( ORDER BY 发送时间 ) + 1000000 AS num
FROM #T
GROUP BY 发送时间
HAVING COUNT(1) > 1
)
UPDATE #T
SET SMS_SERIALS_ID = cte.num
FROM cte
WHERE #T.发送时间 = cte.发送时间
SELECT * FROM #T
update b
set b.SMS_SERIALS_ID=1000000+c.rn
from FMS.MSG.SMS_MESSAGE_SEND_MAIN b
inner join (select a.SEND_TIME,
rn=row_number() over(order by a.SEND_TIME)
from (select distinct SEND_TIME
from FMS.MSG.SMS_MESSAGE_SEND_MAIN) a) c on b.SEND_TIME=c.SEND_TIME