Блог

Надеюсь, просто быстрый вопрос. У меня возникли некоторые трудности с использованием row_number() в инструкции UPDATE.

Предполагая, что у меня есть таблица bookshelf:

 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 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-запрос очень хорошо работает для отображения countrow в столбце temp. Я хочу сохранить все эти выходные данные. Итак, я изменил оператор на такой:

 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
 

Похоже, этот запрос не работает должным образом. Он обновляет все ячейки в countrow с неправильными значениями. Я бы поискал в Google и нашел что-то полезное, например, SqlHack Rowcount и справочную страницу SQLServer, но ничего особенного. Кто-нибудь имеет представление о том, что здесь происходит, или у кого-нибудь была подобная проблема?
Как это исправить, чтобы получить ожидаемый результат?

Вы пытаетесь обновить столбец ( 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;
 

Выберите из представления:

 SELECT * FROM vwMyView;
 

Я не тестировал это, но я думаю, вам нужно выполнить обновление join. Я думаю, вам может понадобиться дополнительный фильтр — RN — для обновления только последней записи bookid.

 WITH latestreadtotal AS
(
         SELECT   userid,
                  createddate,
                  bookid,
                  pagenumber,
                  row_number() OVER (partition BY userid, bookid order by bookid) AS rn,
                  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 )
UPDATE bsf
FROM   bookshelf bsf
JOIN   latestreadtotal lrt
ON     bsf.userid = lrt.userid
AND    bsf.createddate = lrt.createddate
AND    bsf.bookid = lrt.bookid
AND    bsf.pagenumber = lrt.pagenumber
AND    lrt.rn = 1
 

Я думаю, вам просто нужно изменить update на x :

 UPDATE x
    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;
 

Ваш код ссылается bookshelf в подзапросе. Однако результат вызывается x . Вероятно, это означает, что bookshelf in update считается отдельной ссылкой. Я должен признать, что SQL Server имеет тайные правила о разрешении ссылок на таблицы в update s. Обычно я просто использую CTE для таких преобразований с одной таблицей:

 WITH toupdate AS (
      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
     )
UPDATE toupdate
    SET countrow = countrow_new;