SQL SERVER存储过程查询表，由一张出入库表查询出当天总库存前后变化

Question

首先有这么一张表，由商品名称，出入库日期，出入库类型，入库数量，出库数量组成

 

USE [CommodityDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_ORDER](
	[COMM_NAME] [nvarchar](50) NULL,
	[ORDER_DATE] [datetime] NULL,
	[ORDER_TYPE] [nvarchar](50) NULL,
	[QTY_IN] [int] NULL,
	[QTY_OUT] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'IN', 10, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'IN', 50, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'OUT', NULL, 20)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'B', CAST(N'2021-11-11 00:00:00.000' AS DateTime), N'IN', 20, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'B', CAST(N'2021-11-11 00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'B', CAST(N'2021-11-11 00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-11 00:00:00.000' AS DateTime), N'IN', 20, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'PUT', 20, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'PUSH', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'C', CAST(N'2021-11-09 00:00:00.000' AS DateTime), N'IN', 50, NULL)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'C', CAST(N'2021-11-11 00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (N'A', CAST(N'2021-11-10 00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO

现在要将其生成查询表的存储过程，变量为商品名称，出入库日期范围（@ORDER_DATE_FROM,@ORDER_DATE_TO）

查询表由商品名称，出入库日期，出入库类型，历史商品库存，当天日期（内容为当天总出入库数量），当天库存

 代码卡在出入库类型上，导致第5行本应为60变成了80，难点应该是当天库存

注意考虑要求当天总出入库数量，即当天2张出入库数量应合并同一日期显示

 

Answer 1

你是不是没有 PUT 和 PUSH 计算呀。