SQL查询求助-历史最大库存问题

Question

本帖最后由 uu3131313131 于 2013-08-06 15:22:55 编辑

Answer 1

一楼帖子不能编辑，因此在这里贴一下自己考虑的代码，请谅解。
2.具体方法分析：【若同一天有进有出，默认为先进后出】
入库是增加库存的，所以历史最高库存只产生于有入库的那天，因此可以分为2步解决问题：
（1）求出有入库的每天最高库存
（2）所有有入库的最高库存求MAX呃本人的思路想计算出每一天的历史最高库存，当天库存+之前的累计库存-该天之前的所有出库数量然后奋斗了N久，只能写成这样：
SELECT T4.货号,T4.总入库-T3.总出库 历史最高库存
FROM
(
SELECT *
FROM
(
SELECT T.货号,SUM(T.入库数量) 总入库
FROM
(
select 入库日期,货号,sum(数量) 入库数量
from 库存
group by 入库日期,货号
) T
GROUP BY T.货号
) T2
) T4JOIN (
SELECT T1.货号,SUM(T1.出库数量) 总出库
FROM
(
select 出库日期,货号,sum(数量) 出库数量
from 库存
WHERE 出库日期<(SELECT MAX(入库日期) FROM 库存)
group by 出库日期,货号
) T1
GROUP BY T1.货号
) T3ON T4.货号=T3.货号

Answer 2

--货号   数量     入库日期        出库日期
WITH a1 (mate_no,qty,in_date,out_date) AS
(
SELECT 1, 1,'20130701','20130710' UNION all
SELECT 1, 2,'20130702','20130704' UNION all
SELECT 1, 3,'20130703','20130708' UNION all
SELECT 1, 4,'20130705','20130706' UNION all
SELECT 2, 1,'20130701','20130703' UNION all
SELECT 2, 2,'20130702','20130705' UNION all
SELECT 2, 3,'20130703','20130704'
)
,a2 AS
(
SELECT mate_no,qty,in_date date_d
FROM a1
UNION ALL
SELECT mate_no,-qty qty,out_date
FROM a1
)
,a3 AS
(
SELECT mate_no,date_d,SUM(qty) qty
FROM a2
GROUP BY mate_no,date_d
)
,a4 AS
(
SELECT *,(SELECT SUM(qty) FROM a3 WHERE mate_no=a.mate_no AND date_d<=a.date_d) end_qty
FROM a3 a
)
SELECT mate_no,MAX(end_qty)
FROM a4
GROUP BY mate_no

Answer 3

CREATE TABLE #temp(货号 INT, 数量 INT, 入库日期 DATETIME,  出库日期 DATETIME)
INSERT #temp
select '1', '1', '20130701', ' 20130710' union all
select '1', '2', '20130702', ' 20130704' union all
select '1', '3', '20130703', ' 20130708' union all
select '1', '4', '20130705', ' 20130706' union all
select '2', '1', '20130701', ' 20130703' union all
select '2', '2', '20130702', ' 20130705' union all
select '2', '3', '20130703', ' 20130704'--看一下是不是你想要的结果:
;WITH cte AS
(
SELECT 货号,数量,日期=入库日期 FROM #temp
UNION ALL
SELECT 货号,数量=-数量,出库日期 FROM #temp
--ORDER BY 货号, 日期
),
cte1 AS
(
SELECT
货号,
当前库存=(SELECT SUM(数量) FROM cte B WHERE b.货号=a.货号 AND b.日期<=a.日期),
日期
FROM cte A
)
SELECT B.* FROM
(SELECT DISTINCT 货号 FROM #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM cte1 m WHERE m.货号=a.货号 ORDER BY m.当前库存 DESC) b/*
货号 当前库存 日期
1 8 2013-07-05 00:00:00.000
2 5 2013-07-03 00:00:00.000
*/

Answer 4

CREATE TABLE #temp(货号 INT, 数量 INT, 入库日期 DATETIME,  出库日期 DATETIME)
INSERT #temp
select '1', '1', '20130701', ' 20130710' union all
select '1', '2', '20130702', ' 20130704' union all
select '1', '3', '20130703', ' 20130708' union all
select '1', '4', '20130705', ' 20130706' union all
select '2', '1', '20130701', ' 20130703' union all
select '2', '2', '20130702', ' 20130705' union all
select '2', '3', '20130703', ' 20130704'--如果当天有进有出，当天最大库存，只算进不算出，则:
;WITH cte AS
(
SELECT 货号,数量,日期=入库日期 FROM #temp
UNION ALL
),
cte1 AS
(
SELECT
货号,
当前库存=(SELECT SUM(数量) FROM cte B WHERE b.货号=a.货号 AND (b.日期<a.日期 OR (b.日期=a.日期 AND B.数量> 0))),
日期
FROM cte A
)
SELECT B.* FROM
(SELECT DISTINCT 货号 FROM #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM cte1 m WHERE m.货号=a.货号 ORDER BY m.当前库存 DESC) b/*
货号 当前库存 日期
1 8 2013-07-05 00:00:00.000
2 6 2013-07-03 00:00:00.000
*/

Answer 5

从你给出的数据来看：可以不可以理解为1号ID货的历史最高库存为6,
                  2号ID的货的历史最高库存为3呢？？如果是这样的结果，那我想我理解了你的需求

Answer 6

感谢您的关注！
您具体的程序我得再消化消化，
您的程序的运行结果：
mate_no (无列明)
1       8
2       5
与实际的结果有出入。

Answer 7

#7楼，少粘了一行。--如果当天有进有出，当天最大库存，只算进不算出，则:
;WITH cte AS
(
SELECT 货号,数量,日期=入库日期 FROM #temp
UNION ALL
SELECT 货号,数量=-数量,出库日期 FROM #temp
),
cte1 AS
(
SELECT
货号,
当前库存=(SELECT SUM(数量) FROM cte B WHERE b.货号=a.货号 AND (b.日期<a.日期 OR (b.日期=a.日期 AND B.数量> 0))),
日期
FROM cte A
)
SELECT B.* FROM
(SELECT DISTINCT 货号 FROM #temp) a
CROSS APPLY
(SELECT TOP(1) * FROM cte1 m WHERE m.货号=a.货号 ORDER BY m.当前库存 DESC) b/*
货号 当前库存 日期
1 8 2013-07-05 00:00:00.000
2 6 2013-07-03 00:00:00.000
*/

Answer 8

应该是8,6
2号货物：20130703之前的积累是：3
然后20130703当天进库是3，出库是1，因此历史最高纪录是6【货物先进后出】嗯，写个存储过程吧，这样的库存表通常数据量大，CROSS APPLY并不是什么好东西，能不用尽量不用

Answer 9

十分感谢！您能不能在每条CTE 以及 关键地方加入一些注解呢？这样好理解一些。然后我再改改数据试试，有问题再求助大大哈！

Answer 10

应该是8,6
2号货物：20130703之前的积累是：3
然后20130703当天进库是3，出库是1，因此历史最高纪录是6【货物先进后出】嗯，写个存储过程吧，这样的库存表通常数据量大，CROSS APPLY并不是什么好东西，能不用尽量不用感谢您！之前也想写个FUNCTION函数计算累计来着，现在被要求必须一条SQL