从另一个表中的数据更新计数列

In my DB I have two tables Items(Id, ..., ToatlViews int) and ItemViews (id, ItemId, Timestamp)

在我的数据库中我有两个表项（Id，...，ToatlViews int）和ItemViews（id，ItemId，Timestamp）

In ItemViews table I store all views of an item as they come to the site. From time to time I want to call a stored procedure to update Items.ToatlViews field. I tried to do this SP using a cursor ... but the update statement is wrong. Can you help me to correct it? Can I do this without cursor?

在ItemViews表中，我存储项目进入站点时的所有视图。我不时想调用存储过程来更新Items.ToatlViews字段。我尝试使用游标执行此SP ...但更新语句错误。你能帮我纠正一下吗？没有光标我可以这样做吗？

CREATE PROCEDURE UpdateItemsViews
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @currentItemId int
    DECLARE @currentItemCursor CURSOR
    SET @currentItemCursor = CURSOR FOR SELECT Id FROM dbo.Items

    OPEN @currentItemCursor
    FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Update dbo.Items set TotalViews = count(*) 
              from dbo.ItemViews where ItemId=@currentItemId
        FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    END   
END
GO

5 个解决方案

#1

You can use a direct UPDATE statement

您可以使用直接UPDATE语句

update Items set TotalViews = 
     (select COUNT(id) from ItemViews where ItemViews.ItemId = Items.Id)

You might want to test performance for the various ways to do this, if that's important.

如果这很重要，您可能希望测试各种方法的性能。

#2

You could use update ... from instead of a cursor:

您可以使用update ...而不是游标：

update  i
set     TotalViews = iv.cnt
from    dbo.Item i
join    (
        select  ItemId
        ,       count(*) as cnt
        from    dbo.ItemViews
        group by
                ItemId
        ) iv
on      i.Id = iv.ItemId

#3

;WITH x AS 
(
  SELECT ItemID, c = COUNT(*) 
  FROM dbo.ItemViews
  GROUP BY ItemID
)
UPDATE i
SET TotalViews = x.c
FROM dbo.Items AS i
INNER JOIN x
ON x.ItemID = i.ItemID;

But why do you want to store this value, when you can always get the count at runtime? You're going to have to run this update statement every time you touch the ItemViews table in any way, otherwise the count stored with Items is going to be incorrect.

但是，为什么要在运行时始终获得计数时，为什么要存储此值？每次以任何方式触摸ItemViews表时，您都必须运行此更新语句，否则与Items一起存储的计数将不正确。

What you may consider doing instead is setting up an indexed view:

您可能会考虑做的是设置索引视图：

CREATE VIEW dbo.ItemViewCount
WITH SCHEMABINDING
AS
    SELECT ItemID, ItemCount = COUNT_BIG(*)
      FROM dbo.ItemViews
      GROUP BY ItemID;
GO
CREATE UNIQUE CLUSTERED INDEX x ON dbo.ItemViewCount(ItemID);

Now you can join to the view in your queries and know that the count is always up to date (without paying the penalty of scanning for the count of each item). The downside to the indexed view is that you pay that cost incrementally when there are inserts/updates/deletes to the ItemViews table.

现在，您可以加入查询中的视图，并知道计数始终是最新的（无需支付扫描每个项目计数的罚款）。索引视图的缺点是，当对ItemViews表进行插入/更新/删除时，您将逐步支付该成本。

#4

I found this question / answer a year after it was written and answered. the answer was okay, but I was after something a bit more automatic. I ended up writing a trigger to automatically recalculate the column when a relevant row in the other table was inserted, deleted or updated.

在写完和回答一年后，我发现了这个问题/答案。答案还可以，但我有点自动了。我最后写了一个触发器，当插入，删除或更新另一个表中的相关行时自动重新计算列。

I think it's a better solution than running something manually to do the recalculation as there isn't any possibility of someone forgetting to run the code:

我认为这是一个更好的解决方案，而不是手动运行来重新计算，因为没有任何人忘记运行代码的可能性：

CREATE TRIGGER [dbo].[TriggerItemTotalViews] 
   ON  [dbo].[ItemViews]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE [Items] 
SET [TotalViews] = 
    (
    SELECT COUNT(id) 
    FROM [ItemViews] 
    WHERE [ItemViews].[ItemId] = [Items].[ItemId]
    )
WHERE [Items].[ItemId] IN
    (
    SELECT [ItemId] FROM [INSERTED] 
    UNION 
    SELECT [ItemId] FROM [DELETED]
    )
END

#5

Same but different:

相同但不同：

declare @productId int = 24;
declare @classificationTypeId int = 86;

update s
set CounterByProductAndClassificationType = row_num
from Samples s
join
(
    select row_number() over (order by (select Id)) row_num, Id
    from Samples
    where 
        ProductId = @productId and
        ClassificationTypeId = @classificationTypeId
) s_row on s.Id = s_row.Id

#1