[新年第一帖] 求教一SQL 问题, 望大大们,指点.

[雷肿么了]

描述: 按先进先出,批次扣数, 并计算采购总价价格.SQL 语句如下:

--- 批次入库表 ---
CREATE TABLE #TMP01
(
  ID INT,
  SKU_ID INT,
  Num INT,
  Cost INT,
  CREATE_DATE DATETIME	
)			 

INSERT INTO #TMP01 SELECT 1,5896,10,6,'2012-01-02 12:50:03.000'
INSERT INTO #TMP01 SELECT 7,5896,20,5,'2012-01-10 12:50:03.000'
INSERT INTO #TMP01 SELECT 9,5896,30,4,'2012-02-11 12:50:03.000'

INSERT INTO #TMP01 SELECT 2,4255,10,6,'2012-01-07 12:50:03.000'
INSERT INTO #TMP01 SELECT 3,4255,20,5,'2012-01-08 12:50:03.000'

----- 需要扣数的数据 ---
CREATE TABLE #TMP02
(
  ID INT,  --对应#TMP01 表的SKU_ID
  CODE VARCHAR(20),
  Num INT  
)	
INSERT INTO #TMP02 SELECT 4255,'ECV02',12
INSERT INTO #TMP02 SELECT 5896,'PT06',31

SELECT * FROM #TMP01
SELECT * FROM #TMP02

根据SKU_ID 按顺序扣数,并计算采购总价,以及显示物品清单
最终结果:
ID SKU_ID Num Cost CREATE_DATE
1 5896 0 6 2012-01-02 12:50:03.000
7 5896 0 5 2012-01-10 12:50:03.000
9 5896 29 4 2012-02-11 12:50:03.000
2 4255 0 6 2012-01-07 12:50:03.000
3 4255 18 5 2012-01-08 12:50:03.000

采购总价,以及扣数的清单(方便以后查询):
采购总价:(10*6+20*5+1*4)+(10*6+2*5)
扣数清单: PT06*10*6,PT06*20*5,PT06*1*4,ECV02*10*6,ECV02*2*5

[szm341]

你这num中的0、0、29、0、18这几个数是怎么来的？

[着魔]

不明白你想要什么，这不是你要的结果？？？

[雷肿么了]

不好意思,没描述清楚... #TMP02 中的ID 与#TMP01 表中的SKU_ID关联. #TMP01中按顺序扣掉 等于#TMP02中Num 的个数.. 比如:#TMP01表中SKU_ID =5896 中Num的记录是 10 20 30 但#TMP02中ID=5896 的Num=31,所以按顺序扣完之后是 0 0 29 不知这样清晰否?

[szm341]

;with cte as
(select a.*,row_number() over(partition by a.sku_id order by a.id)rn,b.num as diffnum 
from #TMP01 a inner join #TMP02 b on a.sku_id=b.id
)
,cte1 as
(
select *,case when num<diffnum then diffnum-num else 0 end as rz,case when num>diffnum then num-diffnum else 0 end as newnum
from cte where rn=1
union all
select b.*,case when b.num<a.rz then a.rz-b.num else 0 end as rz,case when b.num>a.rz then b.num-a.rz else 0 end as newnum
from cte1 a inner join cte b on a.sku_id=b.sku_id and a.rn=b.rn-1
)
select ID,SKU_ID,newnum,Cost,CREATE_DATE from cte1 order by sku_id

/*
ID          SKU_ID      newnum      Cost        CREATE_DATE
----------- ----------- ----------- ----------- -----------------------
2           4255        0           6           2012-01-07 12:50:03.000
3           4255        18          5           2012-01-08 12:50:03.000
1           5896        0           6           2012-01-02 12:50:03.000
7           5896        0           5           2012-01-10 12:50:03.000
9           5896        29          4           2012-02-11 12:50:03.000
*/

[雷肿么了]

结果不清晰, 我再帖次.

ID	SKU_ID	Num	Cost	CREATE_DATE
1	5896	0	6	2012-01-02 12:50:03.000
7	5896	0	5	2012-01-10 12:50:03.000
9	5896	29	4	2012-02-11 12:50:03.000
2	4255	0	6	2012-01-07 12:50:03.000
3	4255	18	5	2012-01-08 12:50:03.000