SQLSERVER 对数据进行排序

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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值