sql - 如何遍历表并更新多行
问题描述
嗨,根据下表,我有 2 个表,其中名称是销售记录历史,另一个是销售信息。基于销售信息表 josh 已售出 100 个我如何更新以销售记录历史表
设想:
josh 套餐一已经售出 50 如何将销售信息中的 100 添加到套餐 1 50 添加到已售,因为最大上限为 100 并且已售余额添加到 josh 的套餐 2
table - sell Record history
name package max cap sold
Josh 1 100 0
Jack 1 100 0
Josh 2 100 0
Austin 1 100 0
table -sell info
name sold
Josh 150
Jack 0
Austin 0
预期输出将是:
name |package|max cap |sold
Josh |1 |100 |100
Jack |1 |100 |0
Josh |2 |100 |50
Austin |1 |100 |0
解决方案
没有办法在一个简单的查询中做到这一点。下面的查询 i 使用递归 CTE。
假设:包是数字的,从 1 开始,无间隙地递增 1
-- Create sample table
declare @sell_record table
(
name varchar(6),
package int,
max_cap int,
sold int
)
declare @sell_info table
(
name varchar(6),
sold int
)
-- Insert sample data
insert into @sell_record select 'Josh', 1, 100, 0
insert into @sell_record select 'Jack', 1, 100, 0
insert into @sell_record select 'Josh', 2, 100, 0
insert into @sell_record select 'Austin', 1, 100, 0
insert into @sell_info select 'Josh', 150
insert into @sell_info select 'Jack', 0
insert into @sell_info select 'Austin', 0
-- The query. rcte is recursive cte
; with rcte as
(
-- anchor member. Starts with package 1
select i.name, r.package,
-- allocation of sold_qty to package
alloc = case when i.sold >= r.max_cap - r.sold
then r.max_cap - r.sold
else i.sold
end,
-- balance of the sold qty after allocated
bal_sold = case when i.sold >= r.max_cap - r.sold
then i.sold - r.max_cap - r.sold
else 0
end
from @sell_info i
inner join @sell_record r on i.name = r.name
where r.package = 1
union all
-- recursive member
select c.name, r.package,
-- allocation of sold_qty to package
alloc = case when c.bal_sold >= r.max_cap - r.sold
then r.max_cap - r.sold
else c.bal_sold
end,
-- balance of the sold qty after allocated
bal_sold = case when c.bal_sold >= r.max_cap - r.sold
then c.bal_sold - r.max_cap - r.sold
else 0
end
from rcte c
inner join @sell_record r on c.name = r.name
and c.package = r.package - 1
)
-- Update back sell_record
update r
set sold = c.alloc
from rcte c
inner join @sell_record r on c.name = r.name
and c.package = r.package
-- show the result
select *
from @sell_record
/*
name package max_cap sold
Josh 1 100 100
Jack 1 100 0
Josh 2 100 50
Austin 1 100 0
*/
推荐阅读
- ruby-on-rails - 用于捕获给定字符串的特定值的 ruby 正则表达式可以是什么?
- python - 是否有一个函数可以找到忽略峰值的曲线斜率?
- string - Groovy:将字符串转换为整数会产生 NumberFormatException
- javascript - 功能 NVZ 在地图上加载需要更多时间
- amazon-web-services - 具有资源标签条件的 AWS Instance Connect
- android - 使用单个命令从多模块项目运行所有 UI/单元测试
- javascript - 编写单元测试用例时,箭头函数和普通函数有什么区别?
- javascript - Google Sheets API 适用于一张纸,但不适用于另一张
- python-3.x - 有没有更简单的方法可以将一串数字序列转换为python中的列表?
- c# - 首先在 EFCore 3.1.1 代码中播种数据后,是否可以将标识值设置为最后插入的 id + 1?