如何在 SQL Server 2008 中的存储过程中编写游标

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我的数据库中有两个表

优惠券表

  • id (int)
  • 名称 (nvarchar(max))
  • NoofUses (int)

优惠券使用表

  • id(int)
  • 优惠券(int)
  • 创建日期(日期时间)

每当用户点击优惠券时，都会有一个条目进入包含该优惠券 ID 的 CouponUse 表

Whenever a user clicks on a coupon an entry goes into the CouponUse table containing that Coupon's id

现在在 coupon 表中有一个名为 NoofUses 的列.我想在存储过程中编写一个游标，该过程循环 couponuse 表并查看一张优惠券有多少行，并在优惠券的 NoofUses 字段中填写该数字.

Now there is a column in the coupon table called NoofUses. I want to write a cursor inside a stored procedure which loops over couponuse table and sees how many rows are there for one coupon and fill that number in NoofUses field in coupon.

我有这个查询

  select COUNT(*) as totalcount , Name as name from Coupon as coupon 
  join CouponUse as couponuse on coupon.id = couponuse.couponid
  group by couponuse.couponid , coupon.Name

它给了我优惠券名称和 couponuse 的计数

which gives me the coupon name and its count from couponuse

但我不知道如何在使用游标的存储过程中实现它?

But I don't know how to implement that in a stored procedure using a cursor ?

您提出的任何问题都将不胜感激，谢谢

Anything you ask about question will be appreciated , Thanks

推荐答案

仅仅使用一个简单的 UPDATE 语句有什么问题??

What's wrong with just simply using a single, simple UPDATE statement??

UPDATE dbo.Coupon
SET NoofUses = (SELECT COUNT(*) FROM dbo.CouponUse WHERE Couponid = dbo.Coupon.ID)

这就是所需要的！没有凌乱复杂的游标，没有循环，没有 RBAR(逐行痛苦)处理......只是一个漂亮、简单、干净的基于集合的 SQL 语句.

That's all that's needed ! No messy and complicated cursor, no looping, no RBAR (row-by-agonizing-row) processing ..... just a nice, simple, clean set-based SQL statement.

相关文章