主页 / dba / 问题 / 317562
Accepted
TAHER El Mehdi
Asked: 2022-09-29 12:17:03 +0800 CST

如何以相反的顺序更新表记录？

  • 772

我有一张桌子学生

Id  Name    Mark
1   Medi    10
2   Ibra    15
3   Simo    20

我想更新它，我只想按降序反转它 Name 和 Mark 并保持 Id 的顺序：

Id  Name    Mark
1   Simo    20
2   Ibra    15
3   Medi    10

所以首先，我将顺序从上到下颠倒row_number()：

SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark] 
FROM [Student] 
ORDER BY [Id] DESC

但我需要的是更新而不仅仅是选择。
所以其次，我尝试更新这两列。

UPDATE students_ordered
set students_ordered.[Name]=students_reversed.[Name],
    students_ordered.[Mark] =students_reversed.[Mark]
from (SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark]
  FROM [test].[dbo].[Student] students_ordered) students_ordered
inner join 
(SELECT row_number() OVER (ORDER BY  [Id]) [Id],[Name],[Mark] 
FROM [Student] 
ORDER BY [Id] DESC) students_reversed
  on students_reversed.Id=students_ordered.Id

我收到一个错误：/ “消息 1033，级别 15，状态 1：ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP、OFFSET 或 FOR XML。”

我没有失去希望并尝试只更新一个字段，然后传递给另一个但是查询是徒劳的：

UPDATE Student Student_set
set Student_set.Name = (SELECT [Name]
FROM [Student] Student_get
where Student_set.id=Student_get.id ORDER BY row_number() OVER (ORDER BY [Id]) DESC)

由于 id 我不能更新它（Id 是一个主键，它加一。）

sql-server query

4 个回答

  1. Best Answer

    你在正确的轨道上，row_number但排序错误。

    我们首先构建并填充表：

    create table #tmp(Id int, name varchar(4), mark int)

insert into #tmp
values (1,'Medi', 10),
(2, 'Ibra', 15),
(3, 'Simo', 20)

    然后使用row_numberand order by mark desc，它会按照您需要的顺序获取行号：

    SELECT *,
       ROW_NUMBER() OVER(
       ORDER BY mark DESC) AS rn
FROM #tmp

    现在我们可以使用更新row_number的键来更新原始表：

     WITH cte
     AS (SELECT *,
                ROW_NUMBER() OVER(
                ORDER BY mark DESC) AS rn
         FROM #tmp)

     UPDATE a
       SET mark = b.mark,
           name = b.name
     FROM #tmp a
     INNER JOIN cte b
          ON a.Id = b.rn;
    • 4

  2. 这里不需要自加入，直接更新CTE即可。

    您也不需要交换所有其他列，只需交换 ID。

    create table #tmp (Id int, name varchar(4), mark int);

insert into #tmp values
(1   ,'Medi',    10),
(2   ,'Ibra',    15),
(3   ,'Simo',    20);

    WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY ID DESC)
    FROM #tmp
)
UPDATE cte
SET ID = rn;

    db<>小提琴

    • 3

  3. 另一种方法 - 没有自我加入。

    在dbfiddle.uk中测试：

    WITH cte AS (
  SELECT *,
    1 + COUNT(1) OVER () 
          - 2 * ROW_NUMBER () OVER (ORDER BY ID)
    AS rn      -- This is not a row_number.
               -- Say that the total number of rows is 7.
               -- That number is calculated by COUNT(1) OVER ().
               -- Then for any row, say the one with row_number = 2
               -- (when we order by id ASC)
               -- we want to exchange its values (name, mark)
               -- with the row that has row_number = 2
               -- (when we order by id DESC).
               -- That would be row_number = 6 (when order by id ASC).
               -- So, how to get from 2 to 6? The difference is 6-2 = 4.
               -- The two rows (2 and 6 in this case) have a sum of 8.
               -- Always. 1+7 = 2+6 = 3+5 = 4+4 = ... = 7+1 = 8
               -- So to find that 6, we need: 8-2 = Total + 1 - Row_number
               -- And to find that wanted 4, we need:
               -- (6-2) = (Total + 1 - Row_number) - Row_number
               -- Finally that is the same as:
               -- Total + 1 - 2 * Row_number
  FROM tmp
),
  
cte2 AS (
  SELECT *,
    -- we cannot use negative step with LEAD() or LAG()
    -- so we use the absolute value
    CASE WHEN rn > 0
      THEN LEAD(name, ABS(rn)) OVER (ORDER BY id)
      ELSE  LAG(name, ABS(rn)) OVER (ORDER BY id)
    END AS name_r,
    CASE WHEN rn > 0
      THEN LEAD(mark, ABS(rn)) OVER (ORDER BY id)
      ELSE  LAG(mark, ABS(rn)) OVER (ORDER BY id)
    END AS  mark_r
  FROM cte
)
UPDATE cte2
SET name = name_r,
    mark = mark_r ;
    • 2

  4. 根据您的描述，我的回答是：“不要这样做”。

    关系数据库的基本前提之一是数据可以无序存储。选择结果时，您始终可以对结果进行排序。如果他们的身份，他们的等级或顺序应该是独立的。

    如果学生的分数或成绩发生变化或被更改，会发生什么情况？你每次都要重写整个表吗？这将是低效的，并且是大规模的噩梦。

    • 1

相关问题