22,300
社区成员




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]
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]
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)
CREATE PROCEDURE sp_1
@dt datetime -- 约定只输入年月日,不做校验
AS
BEGIN TRAN
INSERT INTO B(Name, OrderID)
SELECT Name, OrderID
FROM A
WHERE CreateAt >= @dt
AND CreateAt < DateAdd(day,1,@dt)
UPDATE C
SET availableNum = availableNum + A.Num
, OccupyNum = OccupyNum - A.Num
FROM C
JOIN A
ON C.SID = A.SID
WHERE A.CreateAt >= @dt
AND A.CreateAt < DateAdd(day,1,@dt)
COMMIT TRAN
GO
DECLARE @d DATETIME=DATEADD(d,-4,GETDATE())
INSERT INTO B
SELECT DISTINCT Name,OrderID from A
WHERE DATEDIFF(d,CreateAt,@d)=0 AND NOT EXISTS(SELECT 0 FROM B WHERE b.name=a.name AND b.OrderID=a.OrderID)
GO
update c SET c.availableNum=c.availableNum+a.Num,c.OccupyNum=c.OccupyNum-a.num
FROM c INNER JOIN A ON a.SID=c.SID
WHERE DATEDIFF(d,a.CreateAt,@d)=0