分享
三行代码  ›  专栏  ›  技术社区  ›  NHSnowSkier

需要对SQL Server中的列重新排序

  •  0
  • NHSnowSkier  · 技术社区  · 2 天前

    我有一个表,其中有一列用来对行排序。我需要能够重新排序该列,同时尊重(有时是无序的)现有数据。

    这是我的起始数据;

    +-------+-------+---------+
    | id    | mySeq | myText  |
    +-------+-------+---------+
    |  1    | 1     | First   |
    +-------+-------+---------+
    |  2    | 99    | -->Last |
    +-------+-------+---------+
    |  3    | 3     | Third   |
    +-------+-------+---------+
    |  4    | 4     | Fourth  |
    +-------+-------+---------+
    

    这就是我想要的结局;

    +-------+-------+---------+
    | id    | mySeq | myText  |
    +-------+-------+---------+
    |  1    | 10    | First   |
    +-------+-------+---------+
    |  2    | 40    | -->Last |
    +-------+-------+---------+
    |  3    | 20    | Third   |
    +-------+-------+---------+
    |  4    | 30    | Fourth  |
    +-------+-------+---------+
    

    /****** Object:  Sequence [dbo].[CountBy10]    Script Date: 11/21/2020 10:12:27 PM ******/
    CREATE SEQUENCE [dbo].[CountBy10] 
     AS [int]
     START WITH 10
     INCREMENT BY 10
    GO
    

    这是我最近的尝试 (这将重新排序,但不考虑原始“mySeq”列的顺序,它正在按“id”列的顺序处理行。

    ALTER SEQUENCE CountBy10  
    RESTART WITH 10;
    UPDATE TestTable  set mySeq = next value for CountBy10
    WHERE EXISTS (select top(9999) id from TestTable order by mySeq);
    

    不幸的是,sqlserver不允许在更新时包含“orderby”子句。

    1 回复  |  直到 2 天前
        1
  •  0
  •   Gordon Linoff    2 天前

    使用可更新的CTE:

    with toupdate as (
          select t.*, row_number() over (order by myseq) as seqnum
          from testtable t
         )
    update toudpate
        set myseq = seqnum * 10;