SQL-购进库存记录

题目
有如下2张表

• F0128A发药信息表
  

• F0128B购进记录表
  
  某医院系统卖给患者阿莫西林X数量后去下购进记录的库存，购进记录可能是零散的。现在按iilszh排序，优先iilszh小的，依次下库存如何得到类似下面的结果集：
  
  测试语句

CREATE TABLE F0128A (
-- iilszh INT IDENTITY(1,1) -- SQL server
iilszh INT AUTO_INCREMENT -- MySQL
,spmc VARCHAR(100) --药品名称
,fysl NUMERIC(18,4) --发药数量
)
INSERT INTO F0128A (spmc, fysl) VALUES ('阿莫西林胶囊',80);
INSERT INTO F0128A (spmc, fysl) VALUES ('人血白蛋白',100);


CREATE TABLE F0128B (
-- iilszh INT IDENTITY(1,1) -- SQL server
iilszh INT AUTO_INCREMENT -- MySQL
,spmc VARCHAR(100) --药品名称
,gjsl NUMERIC(18,4) --购进数量
)
INSERT INTO F0128B (spmc, gjsl) VALUES ('阿莫西林胶囊',20);
INSERT INTO F0128B (spmc, gjsl) VALUES ('阿莫西林胶囊',50);
INSERT INTO F0128B (spmc, gjsl) VALUES ('阿莫西林胶囊',40);
INSERT INTO F0128B (spmc, gjsl) VALUES ('阿莫西林胶囊',30);
INSERT INTO F0128B (spmc, gjsl) VALUES ('人血白蛋白',120);
INSERT INTO F0128B (spmc, gjsl) VALUES ('人血白蛋白',80);
INSERT INTO F0128B (spmc, gjsl) VALUES ('人血白蛋白',100);

解法

SELECT 
spmc AS 药品名称, 
gjsl AS 购进数量, 
(gjsl - (CASE
WHEN diff_sl < 0 THEN 0 
WHEN diff_sl > gjsl THEN gjsl
ELSE diff_sl END)) AS 本次库存数量, 
CASE
	WHEN diff_sl < 0 THEN 0 
	WHEN diff_sl > gjsl THEN gjsl
	ELSE diff_sl END 剩余数量 
FROM(
	SELECT *,(cum_gjsl - fysl) AS diff_sl 
	FROM (
		SELECT a.*,b.fysl FROM(
			SELECT *, 
			SUM(gjsl) OVER (PARTITION BY spmc ORDER BY iilszh) AS cum_gjsl 
			FROM f0128b 
			ORDER BY iilszh ) a
		JOIN (SELECT spmc, fysl FROM f0128a) b
		ON a.spmc = b.spmc) c
	ORDER BY iilszh, diff_sl) d