求一个先进先出的运算SQL语句

[kuaile999]

-----------------------------------------
Stock (库存表)
-----------------------------------------
StockID (库存ID)
Count (库存数量)
------------------------------------------

-----------------------------------------
StockID Count
1 500.0000
2 300.0000
-----------------------------------------



------------------------------------------
StockIn (入库表)
------------------------------------------
StockInID (日期序号ID,越大表示入库日期越晚)
StockInCount (入库数量)
StockID (库存ID,对应库存表的ID)
------------------------------------------

-------------------------------------------
StockInID StockInCount StockID
1 100.0000 1
2 200.0000 1
3 300.0000 1
4 700.0000 1
5 100.0000 1
6 200.0000 1
7 400.0000 1
8 200.0000 1
9 500.0000 2
10 900.0000 2
-------------------------------------------





以库存表 库存表 Stock 第一条记录 为 例
StockID Count
1 500.0000


通过2张表关联运算,想得到以下结果。

StockInID StockInCount StockID 已出库数量 剩余数量
1 100.0000 1 100 0
2 200.0000 1 200 0
3 300.0000 1 300 0
4 700.0000 1 700 0
5 100.0000 1 100 0
6 200.0000 1 200 0
7 400.0000 1 100 300
8 200.0000 1 0 200


根据库存数量推算,得到先进先出记录。这样就可以根据先进先出知道哪一次入库，没进行出库。


谢谢！

[kuaile999]

SELECT a.StockInID,a.StockID,
ISNULL((
SELECT SUM(u.StockInCount) FROM StockIn u
WHERE u.StockID = b.StockID AND u.StockInID < a.StockInID
),0) AS FrstCount,

--ISNULL((
-- SELECT SUM(v.StockInCount) FROM StockIn v WHERE v.StockID = a.StockID
--) - b.Count,0) AS OutCount,b.Count
--,

a.StockInCount,
CASE WHEN
ISNULL((SELECT SUM(u.StockInCount) FROM StockIn u WHERE u.StockID = b.StockID AND u.StockInID <= a.StockInID ),0)
- ISNULL((SELECT SUM(v.StockInCount) FROM StockIn v WHERE v.StockID = a.StockID
) - b.Count,0) > 0
then
ISNULL((SELECT SUM(u.StockInCount) FROM StockIn u WHERE u.StockID = b.StockID AND u.StockInID <= a.StockInID ),0)
- ISNULL((SELECT SUM(v.StockInCount) FROM StockIn v WHERE v.StockID = a.StockID
) - b.Count,0)
-
(
CASE WHEN
ISNULL((SELECT SUM(u.StockInCount) FROM StockIn u WHERE u.StockID = b.StockID AND u.StockInID < a.StockInID ),0)
- ISNULL((SELECT SUM(v.StockInCount) FROM StockIn v WHERE v.StockID = a.StockID
) - b.Count,0) > 0
then
ISNULL((SELECT SUM(u.StockInCount) FROM StockIn u WHERE u.StockID = b.StockID AND u.StockInID < a.StockInID ),0)
- ISNULL((SELECT SUM(v.StockInCount) FROM StockIn v WHERE v.StockID = a.StockID
) - b.Count,0)
else
0
end
)

else
0
end
--,
-- ISNULL((SELECT SUM(u.StockInCount) FROM StockIn u WHERE u.StockID = b.StockID AND u.StockInID < a.StockInID ),0)


FROM StockIn a
LEFT JOIN Stock b ON a.StockID = b.StockID

[二月十六]

;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn 
),cteb AS (
SELECT ctea.*,
       CASE
           WHEN [Count] > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - [Count]
       END AS 已出库数量,
       CONVERT(DECIMAL(18,4),CASE
           WHEN [Count] > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               ctea.StockInCount - [Count]
       END) AS 剩余数量,
	   CONVERT(DECIMAL(18,4),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
    JOIN dbo.Stock
        ON Stock.StockID = ctea.StockID
WHERE rn = 1
      AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
       CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - cteb.tempcount
       END AS 已出库数量,
       CONVERT(DECIMAL(18,4),CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               cteb.tempcount
       END) AS 剩余数量,
	   CONVERT(DECIMAL(18,4),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount 
	   FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID

)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID

[二月十六]

上边那个测试数据有点问题，看看这个，另外这个就读取了一条的，多的话把where条件去掉即可

;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn 
),cteb AS (
SELECT ctea.*,
       CASE
           WHEN [Count] > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - [Count]
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN [Count] > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               ctea.StockInCount - [Count]
       END) AS 剩余数量,
	   CONVERT(DECIMAL(18,10),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
    JOIN dbo.Stock
        ON Stock.StockID = ctea.StockID
WHERE rn = 1
      AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
       CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - cteb.tempcount
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               cteb.tempcount
       END) AS 剩余数量,
	   CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount 
	   FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID

)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID

[kuaile999]

谢谢楼上的老大，我得慢慢看一下。
请教,能否写成一个视图的形式？

[二月十六]

CREATE VIEW view_t
AS 
WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn 
),cteb AS (
SELECT ctea.*,
       CASE
           WHEN [Count] > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - [Count]
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN [Count] > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               ctea.StockInCount - [Count]
       END) AS 剩余数量,
       CONVERT(DECIMAL(18,10),[Count]-ctea.StockInCount) AS tempcount
FROM ctea
    JOIN dbo.Stock
        ON Stock.StockID = ctea.StockID
WHERE rn = 1
      AND ctea.StockID = 1 --读取第一条
UNION ALL
SELECT ctea.*,
       CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - cteb.tempcount
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               cteb.tempcount
       END) AS 剩余数量,
       CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount 
       FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
)
SELECT * FROM cteb

读取view数据

SELECT * FROM dbo.view_t ORDER BY StockInID

[kuaile999]

老大,测试多条的话好像有问题。你看

StockInID StockInCount StockID rn 已出库数量 剩余数量 tempcount
1 100.0000 1 8 100.0000 0.0000 0.0000
2 200.0000 1 7 200.0000 0.0000 0.0000
3 300.0000 1 6 300.0000 0.0000 0.0000
4 700.0000 1 5 700.0000 0.0000 0.0000
5 100.0000 1 4 100.0000 0.0000 0.0000
6 200.0000 1 3 200.0000 0.0000 0.0000
7 400.0000 1 2 100.0000 300.0000 0.0000
8 200.0000 1 1 0.0000 200.0000 300.0000


9 300.0000 2 2 900.0000 -600.0000 0.0000
10 900.0000 2 1 600.0000 600.0000 -600.0000

[二月十六]

这样对吗？

;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY StockID ORDER BY StockInID DESC) rn FROM dbo.StockIn 
),cteb AS (
SELECT ctea.*,
       CASE
           WHEN [Count] > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - [Count]
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN [Count] > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               [Count]
       END) AS 剩余数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN [Count] > ctea.StockInCount THEN
               [Count] -ctea.StockInCount
           ELSE
               0
       END) AS tempcount
FROM ctea
    JOIN dbo.Stock
        ON Stock.StockID = ctea.StockID
WHERE rn = 1
     
UNION ALL
SELECT ctea.*,
       CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               0
           ELSE
               ctea.StockInCount - cteb.tempcount
       END AS 已出库数量,
       CONVERT(DECIMAL(18,10),CASE
           WHEN cteb.tempcount > ctea.StockInCount THEN
               ctea.StockInCount
           ELSE
               cteb.tempcount
       END) AS 剩余数量,
       CONVERT(DECIMAL(18,10),CASE WHEN cteb.tempcount-ctea.StockInCount>0 THEN cteb.tempcount-ctea.StockInCount ELSE 0 END) AS tempcount 
       FROM ctea JOIN cteb ON ctea.rn = cteb.rn+1 AND cteb.StockID = ctea.StockID
 
)
SELECT StockInID,StockInCount,StockID,已出库数量,剩余数量 FROM cteb ORDER BY cteb.StockInID

[kuaile999]

对，没错，谢谢老大了

[二月十六]

引用 7 楼 kuaile999 的回复:

对，没错，谢谢老大了

如果有问题了，可以点击我回帖的“引用”继续问，如果不点击引用，我就看不到了……像你现在这样回帖，我是没提醒的

[早起晚睡]

学习了不错视图走起！！！