每个门店的关键型号清单以及库存的主要资源(如有)或cero
select a.sloc, a.model, a.stock critical, isnull(b.stock,0) 'Main Resources'
from stock a left join stock b on a.model=b.model and b.sloc='Main'
where a.stock<5
关于你请求的最后一部分,我不确定新开的商店需要什么。
编辑
begin transaction
declare @initialStock int=5
declare @stockDecrease int=0
insert into stock (sloc, model, stock)
select (select a.sloc
from stock a
where a.stock is null) n, m.model, @initialStock
from stock m where m.sloc='Main'
set @stockDecrease= @@ROWCOUNT/4*@initialStock
delete stock where stock is null
update stock
set stock=stock-@stockDecrease
where sloc='Main'
if exists (select 1 from stock where stock<0 and sloc='Main')
rollback
else
commit
