数据库脚本,把相同时间的行的一个字段改为同一个

[jiaokangkk]

就是把发送时间相同的两条短信后面的SMS_SERIALS_ID改为1000001,依次类推

[wwfxgm]

这2楼语句，真是写的挺好的。

• 打赏
• 举报

回复 赞

[唐诗三百首]

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

• 打赏
• 举报

回复 赞

[jiaokangkk]

• 打赏
• 举报

回复 赞

[二月十六]

--测试数据
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

• 打赏
• 举报

回复 赞