据说下句能根据当前字段,求出下一条记录.但是执行后有错误提示,错误是:不能往新表#中添加identity字段,因为原表A已经有编号字段id了.这个问题如何解决? 表A(id号排序不整齐): id city people
1 a 10
4 b 30
3 a 50
2 b 30 如果知道"4 b 30" ,如何求出"3 a 50" ?select identity(int,1,1) as guid,* into # from Aselect *
from #
where guid = (select guid+1 from # where id = 4)
1 a 10
4 b 30
3 a 50
2 b 30 如果知道"4 b 30" ,如何求出"3 a 50" ?select identity(int,1,1) as guid,* into # from Aselect *
from #
where guid = (select guid+1 from # where id = 4)
(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS RN,* FROM TA
)
SELECT * FROM CTE WHERE RN=(SELECT RN+1 FROM CTE WHERE id = 4)
2. update newid = id
3. drop id column
然后继续你的操作
;WITH CTE AS
(
SELECT ROW_NUMBER()OVER(ORDER BY id) AS RN,* FROM TA
)
SELECT * FROM CTE WHERE RN=(SELECT RN+1 FROM CTE WHERE id = 4)修改了一下2楼的