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

uu3131313131 2013-08-06 03:13:14
小小新人一枚，下面这道题目想而来好久也没能做出来，特地来向各位求教，请各位大大们不吝赐教：

数据表格名称：库存
货号 数量 入库日期 出库日期
————————————————————
1 1 20130701 20130710
1 2 20130702 20130704
1 3 20130703 20130708
1 4 20130705 20130706
2 1 20130701 20130703
2 2 20130702 20130705
2 3 20130703 20130704

要求：写出一条SQL语句，返回货号，历史最大库存。

【下方是自己的理解，若您已经理解题意可以跳过】
个人解读：
1.数据解读：就第一行数据来说：一个货物20130701进库，20130710出库，依次类推；历史最大库存，即这些日期中最大有库存的一天。
这里有一个绕人的地方：举例1：仓库一天进1个货物，出一个货物，那么今天最高库存是？答：1个。
举例2：仓库一天上午进4个，下午出2个，历史最高库存是？答：2个。
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
) T4

JOIN

(
SELECT T1.货号,SUM(T1.出库数量) 总出库
FROM
(
select 出库日期,货号,sum(数量) 出库数量
from 库存
WHERE 出库日期<(SELECT MAX(入库日期) FROM 库存)
group by 出库日期,货号
) T1
GROUP BY T1.货号
) T3

ON T4.货号=T3.货号

这样仅仅能实现部分功能：历史最高库存恰恰是最后入库日期当天。

求各位指点赐教！
...全文
331 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 15 楼 chen357313771 的回复:
[quote=引用 14 楼 uu3131313131 的回复:] [quote=引用 10 楼 chen357313771 的回复:] 不对 ，应该是8,3
应该是8,6 2号货物：20130703之前的积累是：3 然后20130703当天进库是3，出库是1，因此历史最高纪录是6【货物先进后出】[/quote]嗯，写个存储过程吧，这样的库存表通常数据量大，CROSS APPLY并不是什么好东西，能不用尽量不用[/quote] 感谢您！之前也想写个FUNCTION函数计算累计来着，现在被要求必须一条SQL
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 13 楼 wwwwgou 的回复:
#7楼，少粘了一行。
十分感谢！您能不能在每条CTE 以及 关键地方加入一些注解呢？这样好理解一些。 然后我再改改数据试试，有问题再求助大大哈！
chen357313771 2013-08-06
  • 打赏
  • 举报
 回复
引用 14 楼 uu3131313131 的回复:
[quote=引用 10 楼 chen357313771 的回复:] 不对 ，应该是8,3
应该是8,6 2号货物：20130703之前的积累是：3 然后20130703当天进库是3，出库是1，因此历史最高纪录是6【货物先进后出】[/quote]嗯，写个存储过程吧，这样的库存表通常数据量大，CROSS APPLY并不是什么好东西，能不用尽量不用
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 10 楼 chen357313771 的回复:
不对 ，应该是8,3
应该是8,6 2号货物：20130703之前的积累是：3 然后20130703当天进库是3，出库是1，因此历史最高纪录是6【货物先进后出】
Shawn 2013-08-06
  • 打赏
  • 举报
 回复
#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
*/
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 5 楼 lzw_0736 的回复:
--货号 数量 入库日期 出库日期


感谢您的关注！
您具体的程序我得再消化消化，
您的程序的运行结果：
mate_no (无列明)
1 8
2 5
与实际的结果有出入。
chen357313771 2013-08-06
  • 打赏
  • 举报
 回复
引用 9 楼 uu3131313131 的回复:
[quote=引用 8 楼 chen357313771 的回复:] 从你给出的数据来看：可以不可以理解为1号ID货的历史最高库存为6, 2号ID的货的历史最高库存为3呢？？如果是这样的结果，那我想我理解了你的需求
感谢您的关注！ 不是这样的，就数据来分析的话：1号最高库存是8,2号最高库存是6[/quote]8,5应该对的，糊涂了
chen357313771 2013-08-06
  • 打赏
  • 举报
 回复
不对 ，应该是8,3
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 8 楼 chen357313771 的回复:
从你给出的数据来看：可以不可以理解为1号ID货的历史最高库存为6, 2号ID的货的历史最高库存为3呢？？如果是这样的结果，那我想我理解了你的需求
感谢您的关注！ 不是这样的，就数据来分析的话：1号最高库存是8,2号最高库存是6
chen357313771 2013-08-06
  • 打赏
  • 举报
 回复
从你给出的数据来看：可以不可以理解为1号ID货的历史最高库存为6, 2号ID的货的历史最高库存为3呢？？如果是这样的结果，那我想我理解了你的需求
Shawn 2013-08-06
  • 打赏
  • 举报
 回复 
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
*/
Shawn 2013-08-06
  • 打赏
  • 举报
 回复 
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
*/
lzw_0736 2013-08-06
  • 打赏
  • 举报
 回复
--货号 数量 入库日期 出库日期 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
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
一楼帖子不能编辑，因此在这里贴一下自己考虑的代码，请谅解。 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
	) T4

JOIN

	(
	SELECT T1.货号,SUM(T1.出库数量) 总出库
	FROM
		(
		select 出库日期,货号,sum(数量) 出库数量
		from 库存
		WHERE 出库日期<(SELECT MAX(入库日期) FROM 库存)
		group by 出库日期,货号
		) T1
	GROUP BY T1.货号
	) T3

ON T4.货号=T3.货号
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
不好意思，上面自己理解分析过程中出现一个手打错误。由于自己编辑不了帖子，只能在这里更新。 举例2：仓库一天上午进4个，下午出2个，历史最高库存是？答：4个。
uu3131313131 2013-08-06
  • 打赏
  • 举报
 回复
引用 1 楼 wwwwgou 的回复:
你给的数据中的数量是指：出库数量-入库数量了吗？
感谢您的关注！ 数据中的数量是这个意思： 货号 数量 入库日期 出库日期 ———————————————————— 1 1 20130701 20130710 表示20130701进入仓库1个1号货物，该货物于20130710出仓库。
Shawn 2013-08-06
  • 打赏
  • 举报
 回复
你给的数据中的数量是指：出库数量-入库数量了吗？
MS-SQL Server 基础类 - SQL语句
 
sql语言的艺术
-----------------------Page1----------------------- SQL SSQQLL语言艺术 内容介绍 本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则进行解释说明。这些例 子大多来自于实际案例，对九种SQL经...
SQL Server全程
Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='00...
SQL Server 2005 Data Mining简介
简介    企业均在尝试分析其数据时都面临若干问题。通常，并不缺乏数据。事实上，很多企业感觉到他们被数据淹没了；他们没有办法完全利用所有的数据，将其变成信息。为了处理这方面的问题，开发了数据仓库技术，以让企业从异构的数据源获取大量数据，并将其变成实用信息。    恰当实现的数据仓库是具有惊人强大功能的解决方案。公司可以对信息进行分析，并将其加以利用，以进行明智的决策。通过使用数据仓库，可以为您提供
SQL Server 2005 Data Mining简介[转]
SQL Server 2005 Data Mining简介[转]
简介
    企业均在尝试分析其数据时都面临若干问题。通常，并不缺乏数据。事实上，很多企业感觉到他们被数据淹没了；他们没有办法完全利用所有的数据，将其变成信息。为了处理这方面的问题，开发了数据仓库技术，以让企业从异构的数据源获取大量数据，并将其变成实用信息。
    恰当实现的数据仓库是具有惊人强大功能的解决方案。公司可以对信息进行分析，并将其加以利用，以进行明智的决策。通过使用数据仓库，可以为您提供以下问题的答
MS-SQL Server

34,594

社区成员

254,589

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章