问题描述
我的数据库中有两个表
优惠券表
- 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.
这篇关于如何在 SQL Server 2008 中的存储过程中编写游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！