A表
CREATE TABLE [dbo].[A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[OrderID] [nvarchar](50) NULL,
[CreateAt] [datetime] NULL,
[State] [char](10) NULL,
[Num] [int] NULL,
[SID] [int] NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
B表
CREATE TABLE [dbo].[B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[OrderID] [nvarchar](50) NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
C表
CREATE TABLE [dbo].[C](
[offerID] [int] IDENTITY(1,1) NOT NULL,
[TotalNum] [int] NULL,
[availableNum] [int] NULL,
[OccupyNum] [int] NULL,
[SID] [int] NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[offerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
默认测试数据
insert into A(Name,OrderID,CreateAt,State,Num,SID) values('A1','XXX1','2016-12-01 17:11:56.793',0,50,11)
insert into A(Name,OrderID,CreateAt,State,Num,SID) values('A1','XXX1','2016-11-01 17:11:56.793',0,50,11)
insert into C(TotalNum,availableNum,OccupyNum,SID)values(100,50,50,11)
如果今天日期为2016-12-1日的话会查询一条数据，然后将这条数据插入到B表，在对C表的availableNum,OccupyNum进行修改，应该是availableNum=100，OccupyNum=0. A表SID=C表SID
