sql-server - with - stored procedure oracle




從另一個表中的數據更新計數列 (4)

在我的數據庫中有兩個表項(Id,...,ToatlViews int)和ItemViews(id,ItemId,Timestamp)

在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[email protected]currentItemId
        FETCH NEXT FROM @currentItemCursor INTO @currentItemId
    END   
END
GO

你可以使用update ... from代替游標:

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

您可以使用直接的UPDATE語句

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

如果這一點很重要,你可能想要測試性能的各種方法來做到這一點。


相同但不同:

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

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

但是為什麼你要存儲這個值,當你總是可以在運行時獲得計數? 每次以任何方式觸摸ItemViews表時,您都必須運行此更新語句,否則存儲在Items中的計數將不正確。

你可能會考慮做的是建立一個索引視圖:

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);

現在,您可以加入到您的查詢中的視圖,並知道計數始終是最新的(沒有支付掃描每個項目的計數罰款)。 索引視圖的不利之處在於,當ItemViews表中插入/更新/刪除操作時,您將逐步支付該成本。





stored-procedures