sql存储过程

[【Help】]

先有以下3张表，A、B、C，我需要查询A的当天时间的数据，然后查询出来的数据对B的OrderID进行赋值记录，然后将C表中的占用数量=A表中的Num-占用数量，在将C表中的可用数量=Num+占用数量这样进行修改，表结构如下

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

[【Help】]

自己顶下，有人会这个存储过程吗？

[【Help】]

有人知道吗？大神

[道素]

猜你的需求

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

[【Help】]

引用 3 楼 ch21st 的回复:

猜你的需求

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

如果B表的ID是Guid需要怎么改？

[Tiger_Zhao]

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

[【Help】]

引用 5 楼 Tiger_Zhao 的回复:

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

大牛，如果B表的ID是Guid需要怎么改？

[【Help】]

select NEWID() as guid,* from A这样吧，谢谢2位大牛