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

kuaile999 2018-11-23 09:19:47
-----------------------------------------
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


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


谢谢!


...全文
228 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
早起晚睡 2018-11-25
  • 打赏
  • 举报
回复
学习了不错视图走起!!!
二月十六 版主 2018-11-23
  • 打赏
  • 举报
回复
引用 7 楼 kuaile999 的回复:
对,没错,谢谢老大了
如果有问题了,可以点击我回帖的“引用”继续问,如果不点击引用,我就看不到了……像你现在这样回帖,我是没提醒的
kuaile999 2018-11-23
  • 打赏
  • 举报
回复
对,没错,谢谢老大了
二月十六 版主 2018-11-23
  • 打赏
  • 举报
回复
这样对吗?
;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 2018-11-23
  • 打赏
  • 举报
回复
老大,测试多条的话好像有问题。你看

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
二月十六 版主 2018-11-23
  • 打赏
  • 举报
回复
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 2018-11-23
  • 打赏
  • 举报
回复
谢谢楼上的老大,我得慢慢看一下。
请教,能否写成一个视图的形式?
二月十六 版主 2018-11-23
  • 打赏
  • 举报
回复
上边那个测试数据有点问题,看看这个,另外这个就读取了一条的,多的话把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



二月十六 版主 2018-11-23
  • 打赏
  • 举报
回复
;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


kuaile999 2018-11-23
  • 打赏
  • 举报
回复
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧