基于事件名称更新对象计数的SQL查询

Question

想象一下，我是许多书店的老板。我保存了一个数据库，记录了我所有书店里发生的所有事件。两个值得注意的事件是"Book Added“和"Book Removed"，用于将书添加到故事的库存中，以及从商店出售时。示例模式可以是bookstore_id、event_name、` `time。

现在假设我有第二个表，它维护每个书店的当前状态，所以模式应该是bookstore_id，num_books。

我希望能够使用第一个表来获得每个书店的所有“图书添加”事件的计数，减去每个书店的所有“图书删除”事件的计数，然后在第二个表中更新每个书店中的图书数量。

我能想到的唯一方法是使用游标，但我假设有一种更“SQL风格”的方法，它更基于集合，不需要游标。

Answer 1

select bookstore_id
     , sum(case when event_name = "Book Removed" then -1 else 1 end) as "num books"
  from bookstores  
 group by bookstore_id 

如果超过2个事件

select bookstore_id
     , sum(case when event_name = "Book Removed" then -1 
                when event_name = "Book Added"   then  1  
           end) as "num books"
  from bookstores  
 group by bookstore_id 

除非您遇到性能问题，否则我只会将其作为视图

Answer 2

您可以使用GROUP BY子句对事件进行计数。

如果我们创建两个子表，在那里我们分别计算添加的和删除的书，我们可以简单地减去结果并在父表中更新它们。这看起来像这样：

UPDATE      b
SET         b.numbooks = AddedBooks.BooksAdded - RemovedBooks.BooksRemoved
FROM        dbo.Books b
INNER JOIN  (SELECT     be.book_id, count(*) AS BooksAdded
            FROM        dbo.BookEvents be
            WHERE       be.event = 'BookAdded'
            GROUP BY    be.book_id, be.event) AS AddedBooks 
                                    ON b.bookid = AddedBooks.book_id
INNER JOIN  (SELECT     be.book_id, count(*) AS BooksRemoved
            FROM        dbo.BookEvents be
            WHERE       be.event = 'BookRemoved'
            GROUP BY    be.book_id, be.event) AS RemovedBooks  
                                    ON b.bookid = RemovedBooks.book_id

Answer 3

我们可以使用CTE来单独获取细节并对其进行处理。

With CTE_Add AS
(
Select Bkstr_ID, Count(event_Name) As Added From temp Where event = 'Added' Group by Bkstr_ID
), CTE_Rem As
(
Select Bkstr_ID, Count(event_Name) As Removed From temp Where event = 'Removed'      Group by Bkstr_ID
)
Select A.Bkstr_ID,  Added - Removed
From CTE_Add A
Left Join CTE_Rem R On A.Bkstr_ID= R.Bkstr_ID

这将为您提供ID和计数。您可以使用Insert语句代替select语句