1.修改语句
update 表名 SET 排序的字段= rownum from(
select 排序的条件,排序的字段, ROW_NUMBER() over(order by 排序的字段) rownum from 表名 WHERE 排序的条件='...'
) 表名 WHERE 的='...'
[ update Dcl_Decl_Goods SET Goods_No = rownum from(
select SerNum,Goods_No, ROW_NUMBER() over(order by Goods_No) rownum from Dcl_Decl_Goods WHERE SerNum='XH20171000024_1'
) Dcl_Decl_Goods WHERE SerNum='XH20171000024_1']
2.存储过程
CREATE PROC [dbo].[Dcl_Cont_D_No_Sort]
@SerNum VARCHAR(20),
@comsite VARCHAR(10),
@ContId INT
AS
BEGIN
DECLARE @Cont_Detail_Num INT,@Id INT
SET @Cont_Detail_Num = 0
DECLARE IDS CURSOR FOR
SELECT Id FROM Dcl_Decl_Cont_Detail WHERE SerNum = @SerNum AND comsite = @comsite AND Cont_Id=@ContId ORDER BY Cont_Detail_Num
OPEN IDS
FETCH next FROM IDS INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Cont_Detail_Num = @Cont_Detail_Num + 1
UPDATE Dcl_Decl_Cont_Detail SET Cont_Detail_Num = @Cont_Detail_Num WHERE Id = @Id
FETCH IDS INTO @Id
END
CLOSE IDS
DEALLOCATE IDS
END
3.移动后重新排序
CREATE PROC [dbo].[EP_Dec_List_Item_Sort]
@SerNum VARCHAR(20),
@SerNo INT,
@comsite VARCHAR(10),
@MoveType VARCHAR(10),
@MoveG_NO INT,
@MoveID INT
AS
BEGIN
DECLARE @G_NO INT,@id INT
SET @G_NO = 0
--移动Item
IF isnull(@MoveType,'') <> '' and ISNULL(@MoveG_NO,0) > 0 AND ISNULL(@MoveID,0) > 0
BEGIN
IF @MoveType = 'Up'
BEGIN
UPDATE EP_Dec_List_Item SET G_NO = G_NO - 1 WHERE id = @MoveID
UPDATE EP_Dec_List_Item SET G_NO = G_NO + 1 WHERE SerNum = @SerNum AND SerNo = @SerNo AND comsite = @comsite AND G_NO = @MoveG_NO - 1 AND id <> @MoveID
END
ELSE IF @MoveType = 'Down'
BEGIN
UPDATE EP_Dec_List_Item SET G_NO = G_NO + 1 WHERE id = @MoveID
UPDATE EP_Dec_List_Item SET G_NO = G_NO - 1 WHERE SerNum = @SerNum AND SerNo = @SerNo AND comsite = @comsite AND G_NO = @MoveG_NO + 1 AND id <> @MoveID
END
END
DECLARE IDS CURSOR FOR
SELECT id FROM EP_Dec_List_Item WHERE SerNum = @SerNum AND SerNo = @SerNo AND comsite = @comsite ORDER BY G_NO
OPEN IDS
FETCH next FROM IDS INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @G_NO = @G_NO + 1
UPDATE EP_Dec_List_Item SET G_NO = @G_NO WHERE id = @id
FETCH IDS INTO @id
END
CLOSE IDS
DEALLOCATE IDS
END