在更新语句中使用Row_Number（）

debugcn 发表于 Dev

奥利维亚·伦迪（Olivia Lundy）

希望只是一个简单的问题。我难以确定要在UPDATE语句上使用row_number（）的问题。

假设我有一个书架表：

userid  createddate bookid  pagenumber
1       2021-18-01  charper    141
1       2021-17-01  mproust    136
1       2021-17-01  sking       134
1       2021-15-01  charper    128
1       2021-10-01  jausten    122
2       2021-18-01  vwoolf    141
2       2021-17-01  vwoolf    136
2       2021-17-01  charper    134

我要（总和）相同的bookid页码并写入右列。我的预期结果必须是：

userid  createddate      bookid pagenumber  countrow
1         2021-18-01    charper  141        269
1         2021-17-01    mproust  136        136
1         2021-17-01    sking    134        134
1         2021-15-01    charper  128        
1         2021-11-01    jausten  122        122
2         2021-18-01    vwoolf   141        277
2         2021-17-01    vwoolf   136        
2         2021-17-01    charper  134        134

我的SELECT语句

SELECT userid, bookid, pagenumber,
       case when row_number() over (partition by userid, bookid order by bookid) = 1 
            then sum(pagenumber) over (partition by userid, bookid) 
       end as countrow
  from usertable;

上面的sql查询在temp列上显示countrow效果很好。我想存储所有这些输出。所以我改为声明：

UPDATE bookshelf
SET countrow = countrow_new
FROM ( select userid, createddate, bookid, pagenumber,
       case when row_number() over (partition by userid, bookid, order by bookid) = 1
            then sum(pagenumber) over (partition by userid, bookid) 
       end as countrow_new
  from bookshelf
      )x

此查询似乎无法正常工作。它使用错误的值更新计数行中的所有单元格。我搜索到谷歌，发现有用的东西，如SqlHack Rowcount和SqlServer参考页，但没什么。有谁知道这里发生了什么，还是有人有类似的问题？如何解决这个问题以获得预期的结果？

1986年

您正在尝试更新countrow表中不存在的列（），因此您需要将此列添加到原始表中并加入子查询：

/* add the column */
ALTER TABLE usertable ADD countrow INT;

/* do the update */
UPDATE  usertable
SET     countrow = countrow_new
FROM ( 
        SELECT  userid, 
                createddate, 
                bookid, 
                pagenumber,
                CASE 
                    WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1
                    THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
                END AS countrow_new
        FROM    usertable
      ) x
      JOIN usertable u
        ON u.bookid = x.bookid AND u.createddate = x.createddate AND u.userid = x.userid;

以上假设{bookid,createddate,userid}将永远是唯一的

但是，以这种方式将计算结果存储在表中是非常糟糕的设计和坏习惯-您将不得不在每次插入/更新/删除操作时都使用触发器来使列保持最新状态，这对于性能而言是非常糟糕的。

更好的方法是获取原始查询并为其创建视图，然后可以将其视为表使用：

创建视图：

CREATE VIEW vwMyView
AS
SELECT  userid, 
        bookid, 
        pagenumber,
        CASE 
            WHEN ROW_NUMBER() OVER (PARTITION BY userid, bookid ORDER BY bookid) = 1 
            THEN SUM(pagenumber) OVER (PARTITION BY userid, bookid) 
        END AS countrow
FROM    usertable;

从视图中选择：