高难度的数据问题-流水记录表中的进项总数量减去出项总数量之和等于库存记录表中的数量之和，如果流水记录表中计算出来的库存数量总和不等库存表中的数量之和，自动插入。

[szlixiaolong]

--t_im_flow为流水记录表，flow_id自动生成的序列号，voucher_no代表单据编号,oper_date代表操作日期时间,item_no代表商品货号,real_qty代表进项数量或者出项数量,db_no代表进项数量符号+或者出项数量符号-
CREATE TABLE [dbo].[t_im_flow](
[flow_id] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
[voucher_no] [dbo].[u_sheet_no] NULL,
[oper_date] [dbo].[u_date] NULL,
[item_no] [dbo].[u_item_no] NOT NULL,
[real_qty] [dbo].[u_qty] NULL,
[price] [dbo].[u_price] NULL,
[cost_price] [dbo].[u_price] NULL,
[sale_price] [numeric](16, 4) NULL,
[sheet_amt] [dbo].[u_price] NULL,
[branch_no] [dbo].[u_branch_no] NULL,
[d_branch_no] [dbo].[u_branch_no] NULL,
[trans_no] [dbo].[u_trans_no] NULL,
[db_no] [char](1) NOT NULL,
[supcust_no] [char](8) NULL,
[sale_way] [dbo].[u_sale_way] NULL,
[oper_id] [dbo].[u_oper_id] NOT NULL,
[pre_qnty] [dbo].[u_qty] NULL,
[valid_date] [dbo].[u_date] NULL,
[acc_date] [dbo].[u_date] NULL,
[memo] [varchar](40) NULL,
[com_flag] [dbo].[u_com_flag] NULL,
[sup_ly_rate] [decimal](16, 4) NULL,
[pay_flag] [char](1) NULL,
[sup_sale_way] [char](1) NULL,
[sup_no] [char](8) NULL,
[pos_id] [numeric](3, 0) NULL,
[dpfm_type] [char](1) NULL,
[return_rate] [numeric](16, 4) NULL,
[cls_no] [varchar](20) NULL,
[cls_rate] [numeric](16, 4) NULL,
[pp_no] [varchar](20) NULL,
[pp_rate] [numeric](16, 4) NULL,
[order_man] [varchar](4) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[t_im_flow] ADD [combine_sta] [varchar](1) NOT NULL
ALTER TABLE [dbo].[t_im_flow] ADD [sheet_item] [varchar](20) NULL
ALTER TABLE [dbo].[t_im_flow] ADD [re_flag] [char](1) NOT NULL
ALTER TABLE [dbo].[t_im_flow] ADD [produce_date] [datetime] NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[t_im_flow] ADD [item_barcode] [dbo].[u_barcode_no] NULL
ALTER TABLE [dbo].[t_im_flow] ADD [color_code] [dbo].[u_sizecode_no] NULL
ALTER TABLE [dbo].[t_im_flow] ADD [size_code] [dbo].[u_sizecode_no] NULL
/****** Object: Index [PK_T_IM_FLOW] Script Date: 04/01/2017 23:19:21 ******/
ALTER TABLE [dbo].[t_im_flow] ADD CONSTRAINT [PK_T_IM_FLOW] PRIMARY KEY CLUSTERED
(
[flow_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('+') FOR [db_no]
GO

ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT (0) FOR [sup_ly_rate]
GO

ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [pay_flag]
GO

ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [combine_sta]
GO

ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [re_flag]
GO







--t_im_branch_stock为库存表，item_no代表商品货号,branch_no代表门店编号，stock_qty代表库存数量
CREATE TABLE [dbo].[t_im_branch_stock](
[item_no] [dbo].[u_item_no] NOT NULL,
[branch_no] [dbo].[u_branch_no] NOT NULL,
[stock_qty] [dbo].[u_qty] NULL,
[route_qty] [dbo].[u_qty] NULL,
[avg_cost] [dbo].[u_price] NULL,
[cost_amt] [dbo].[u_price] NULL,
[last_inprice] [dbo].[u_price] NULL,
[last_baseprice] [dbo].[u_price] NULL,
[min_qty] [dbo].[u_qty] NOT NULL,
[max_qty] [dbo].[u_qty] NOT NULL,
[acc_qty] [decimal](16, 4) NULL,
[oper_date] [dbo].[u_date] NULL,
[com_flag] [dbo].[u_com_flag] NULL,
CONSTRAINT [PK_T_IM_BRANCH_STOCK] PRIMARY KEY CLUSTERED
(
[item_no] ASC,
[branch_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

要实现的问题：如果流水记录表中计算出来的库存数量总和不等于库存表t_im_branch_stock中的数量之和，自动插入一行数据到流水记录表t_im_flow中，最终实现流水记录表中的进项总数量减去出项总数量之和跟库存记录表中的数量相等。

[szlixiaolong]

如果流水记录表中计算出来的库存数量总和等于库存表t_im_branch_stock中的数量之和，就不用添加任何数据。

引用 楼主 szlixiaolong 的回复:

--t_im_flow为流水记录表，flow_id自动生成的序列号，voucher_no代表单据编号,oper_date代表操作日期时间,item_no代表商品货号,real_qty代表进项数量或者出项数量,db_no代表进项数量符号+或者出项数量符号- CREATE TABLE [dbo].[t_im_flow]( [flow_id] [numeric](14, 0) IDENTITY(1,1) NOT NULL, [voucher_no] [dbo].[u_sheet_no] NULL, [oper_date] [dbo].[u_date] NULL, [item_no] [dbo].[u_item_no] NOT NULL, [real_qty] [dbo].[u_qty] NULL, [price] [dbo].[u_price] NULL, [cost_price] [dbo].[u_price] NULL, [sale_price] [numeric](16, 4) NULL, [sheet_amt] [dbo].[u_price] NULL, [branch_no] [dbo].[u_branch_no] NULL, [d_branch_no] [dbo].[u_branch_no] NULL, [trans_no] [dbo].[u_trans_no] NULL, [db_no] [char](1) NOT NULL, [supcust_no] [char](8) NULL, [sale_way] [dbo].[u_sale_way] NULL, [oper_id] [dbo].[u_oper_id] NOT NULL, [pre_qnty] [dbo].[u_qty] NULL, [valid_date] [dbo].[u_date] NULL, [acc_date] [dbo].[u_date] NULL, [memo] [varchar](40) NULL, [com_flag] [dbo].[u_com_flag] NULL, [sup_ly_rate] [decimal](16, 4) NULL, [pay_flag] [char](1) NULL, [sup_sale_way] [char](1) NULL, [sup_no] [char](8) NULL, [pos_id] [numeric](3, 0) NULL, [dpfm_type] [char](1) NULL, [return_rate] [numeric](16, 4) NULL, [cls_no] [varchar](20) NULL, [cls_rate] [numeric](16, 4) NULL, [pp_no] [varchar](20) NULL, [pp_rate] [numeric](16, 4) NULL, [order_man] [varchar](4) NULL ) ON [PRIMARY] SET ANSI_PADDING OFF ALTER TABLE [dbo].[t_im_flow] ADD [combine_sta] [varchar](1) NOT NULL ALTER TABLE [dbo].[t_im_flow] ADD [sheet_item] [varchar](20) NULL ALTER TABLE [dbo].[t_im_flow] ADD [re_flag] [char](1) NOT NULL ALTER TABLE [dbo].[t_im_flow] ADD [produce_date] [datetime] NULL SET ANSI_PADDING ON ALTER TABLE [dbo].[t_im_flow] ADD [item_barcode] [dbo].[u_barcode_no] NULL ALTER TABLE [dbo].[t_im_flow] ADD [color_code] [dbo].[u_sizecode_no] NULL ALTER TABLE [dbo].[t_im_flow] ADD [size_code] [dbo].[u_sizecode_no] NULL /****** Object: Index [PK_T_IM_FLOW] Script Date: 04/01/2017 23:19:21 ******/ ALTER TABLE [dbo].[t_im_flow] ADD CONSTRAINT [PK_T_IM_FLOW] PRIMARY KEY CLUSTERED ( [flow_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('+') FOR [db_no] GO ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT (0) FOR [sup_ly_rate] GO ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [pay_flag] GO ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [combine_sta] GO ALTER TABLE [dbo].[t_im_flow] ADD DEFAULT ('0') FOR [re_flag] GO --t_im_branch_stock为库存表，item_no代表商品货号,branch_no代表门店编号，stock_qty代表库存数量 CREATE TABLE [dbo].[t_im_branch_stock]( [item_no] [dbo].[u_item_no] NOT NULL, [branch_no] [dbo].[u_branch_no] NOT NULL, [stock_qty] [dbo].[u_qty] NULL, [route_qty] [dbo].[u_qty] NULL, [avg_cost] [dbo].[u_price] NULL, [cost_amt] [dbo].[u_price] NULL, [last_inprice] [dbo].[u_price] NULL, [last_baseprice] [dbo].[u_price] NULL, [min_qty] [dbo].[u_qty] NOT NULL, [max_qty] [dbo].[u_qty] NOT NULL, [acc_qty] [decimal](16, 4) NULL, [oper_date] [dbo].[u_date] NULL, [com_flag] [dbo].[u_com_flag] NULL, CONSTRAINT [PK_T_IM_BRANCH_STOCK] PRIMARY KEY CLUSTERED ( [item_no] ASC, [branch_no] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO

要实现的问题：如果流水记录表中计算出来的库存数量总和不等于库存表t_im_branch_stock中的数量之和，自动插入一行数据到流水记录表t_im_flow中，最终实现流水记录表中的进项总数量减去出项总数量之和跟库存记录表中的数量相等。