仓库通过出入库统计库存，显示结果需按商品、日期、出入库类型分组、上次库存、出入库数量、现库存

建立数据库，插入数据


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

img

求看看，或者换种方法获取库存


;with t as (
    select *,ROW_NUMBER() over(partition by comm_name order by id) as nid 
    from [dbo].[TB_ORDER]
),t1 as (
    select *,0 as BEG_QTY,ISNULL(QTY_IN,0)-ISNULL(QTY_OUT,0) as QTY,ISNULL(QTY_IN,0)-ISNULL(QTY_OUT,0) as END_QTY from t where nid=1
    union all
    select b.*,a.END_QTY,ISNULL(b.QTY_IN,0)-ISNULL(b.QTY_OUT,0),a.END_QTY+ISNULL(b.QTY_IN,0)-ISNULL(b.QTY_OUT,0) from t1 a,t b where a.nid=b.nid-1 and a.COMM_NAME=b.COMM_NAME
)
select * from t1
order by COMM_NAME,ID

img

你是要这样的结果？

其实最终效果是想写成存储过程，而且要合并同一天同一类型的商品进出库数量，列名QTY改为进出库日期

img

SELECT @SQL = @SQL + ',SUM(CASE ORDER_DATE WHEN +'''+CONVERT(varchar,ORDER_DATE,23)+''' THEN ISNULL(QTY_IN,0)-ISNULL(QTY_OUT,0) ELSE 0 END) ['+CONVERT(varchar,ORDER_DATE,23)+'] '
    FROM (SELECT DISTINCT ORDER_DATE FROM TB_ORDER)AS A