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

2018-11-23 09:19:47 289 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

基于ODPS的SQL语句本博文主要是介绍阿里ODPS（Open Data Processing Service）平台上的SQL语言的入门；（一）基本概念 1.ODPS SQL适用于海量数据(TB级别)，实时性要求不高的场合，它的每个作业的准备，提交等阶段要花费较长时间，因此要求每秒处理几千至数万笔事务的业务是不能用ODPS SQL完成的；ODPS SQL采用的是类似于SQL的语法，可以看作是标准SQL的子集，但不阅读详情

-----------------------------------------
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

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

谢谢！

...全文

【SQL高级】，不得不掌握的SQL语句，SQLserver MySQL 掌握SQL语句，数据操作遂心应手。阅读详情

kuaile999

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

早起晚睡 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

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

相关推荐