MYSQL 添加行号将行号写入到主键的列

MYSQL 添加行号

SELECT @rownum:= @rownum + 1 AS rownum, a.*
FROM(SELECT @rownum := 0) t,is_afxt.hk_vehicle a--或者(假设CREATED_TIME日期列数据不重复)
select 
(select count(1)+1 from is_afxt.hk_vehicle b where b.CREATED_TIME < a.CREATED_TIME) rownum ,a.*
from  is_afxt.hk_vehicle a
order by a.CREATED_TIME asc

 将行号写入到主键的列

--①获取行号
with hf 
as(select (select count(1)+1 from is_afxt.hk_vehicle b where b.CREATED_TIME < a.CREATED_TIME) rownum ,a.*from  is_afxt.hk_vehicle aorder by a.CREATED_TIME asc
)
--②将行号写入主键列
update is_afxt.hk_vehicle a left join hf b on a.CREATED_TIME=b.CREATED_TIME
set a.SID =b.rownum