建立数据库,插入数据
USE [DBTEST]
GO
/****** Object: Table [dbo].[TB_ORDER] Script Date: 2021/11/12 16:53:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_ORDER](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ORDER_ID] [nvarchar](50) NULL,
[COMM_NAME] [nvarchar](50) NULL,
[ORDER_DATE] [datetime] NULL,
[ORDER_TYPE] [nvarchar](50) NULL,
[QTY_IN] [int] NULL,
[QTY_OUT] [int] NULL,
CONSTRAINT [PK_ORDER] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TB_ORDER] ON
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (1, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'IN', 10, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (2, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'IN', 50, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (3, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'OUT', NULL, 20)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (4, NULL, N'B', CAST(N'2021-11-11T00:00:00.000' AS DateTime), N'IN', 20, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (5, NULL, N'B', CAST(N'2021-11-11T00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (6, NULL, N'B', CAST(N'2021-11-11T00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (7, NULL, N'A', CAST(N'2021-11-11T00:00:00.000' AS DateTime), N'IN', 10, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (8, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'PUT', 20, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (9, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'PUSH', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (10, NULL, N'C', CAST(N'2021-11-09T00:00:00.000' AS DateTime), N'IN', 50, NULL)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (11, NULL, N'C', CAST(N'2021-11-11T00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
INSERT [dbo].[TB_ORDER] ([ID], [ORDER_ID], [COMM_NAME], [ORDER_DATE], [ORDER_TYPE], [QTY_IN], [QTY_OUT]) VALUES (12, NULL, N'A', CAST(N'2021-11-10T00:00:00.000' AS DateTime), N'OUT', NULL, 10)
GO
SET IDENTITY_INSERT [dbo].[TB_ORDER] OFF
GO
进行结果查询
SELECT COMM_NAME,ORDER_DATE,ORDER_TYPE,
LAG((SELECT SUM(ISNULL(QTY_IN,0))-SUM(ISNULL(QTY_OUT,0)) FROM TB_ORDER A WHERE A.ORDER_DATE <= B.ORDER_DATE AND A.ORDER_TYPE<=B.ORDER_TYPE AND A.COMM_NAME = B.COMM_NAME))OVER (PARTITION BY COMM_NAME ORDER BY COMM_NAME) AS BEG_QTY,
SUM(ISNULL(QTY_IN,0)-ISNULL(QTY_OUT,0)) AS QTY,
(SELECT SUM(ISNULL(QTY_IN,0))-SUM(ISNULL(QTY_OUT,0)) FROM TB_ORDER A WHERE A.ORDER_DATE <= B.ORDER_DATE AND A.ORDER_TYPE<=B.ORDER_TYPE AND A.COMM_NAME = B.COMM_NAME GROUP BY COMM_NAME)AS END_QTY
FROM TB_ORDER B
GROUP BY COMM_NAME,ORDER_DATE,ORDER_TYPE
ORDER BY COMM_NAME,ORDER_DATE ASC
求看看,或者换种方法获取库存