使用SQL查询更改顺序

 收藏

要与SQL查询排序。 有没有更短的方法来更新它,还是应该更新整个表。

我的表如下:

DECLARE @tab TABLE (ID INT IDENTITY, Name VARCHAR(10), Seq INT)

INSERT INTO @tab VALUES('A',1),('B',1),('C',1),('D',3),('E',4),('F',5),('G',6),('H',7),('I',8)

SELECT * FROM @tab ORDER BY Seq

我想更改顶部显示ID为7、8、9的序列显示列。 我想要的输出应该是

DECLARE @tab TABLE (ID INT IDENTITY, Name VARCHAR(10), Seq INT)

INSERT INTO @tab VALUES('A',4),('B',5),('C',6),('D',7),('E',8),('F',9),('G',1),('H',2),('I',3)
SELECT * FROM @tab ORDER BY Seq
回复
  • ased 回复

    ROW_NUMBER is your friend here, just have to compute 2 different ones depending on the name breaking point.

    ;WITH RowNumbers AS
    (
        SELECT 
            T.ID,
            RegularRowNumber = 3 + ROW_NUMBER() OVER (ORDER BY T.Name),
            AfterGRowNumber = -6 + ROW_NUMBER() OVER (ORDER BY T.Name),
            T.Seq,
            T.Name
        FROM 
            @tab AS T
    )
    UPDATE R SET
        Seq = CASE WHEN R.Name >= 'G' THEN AfterGRowNumber ELSE RegularRowNumber END
    FROM
        RowNumbers AS R
    

    结果:

    ID  Name    Seq
    1   A       4
    2   B       5
    3   C       6
    4   D       7
    5   E       8
    6   F       9
    7   G       1
    8   H       2
    9   I       3
    

    Might want to consider using ID to order instead of Name, if appropriate.