sql存储过程

【Help】 2016-12-02 05:06:48
先有以下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
...全文
558 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
【Help】 2016-12-05
  • 打赏
  • 举报
回复
select NEWID() as guid,* from A这样吧,谢谢2位大牛
【Help】 2016-12-05
  • 打赏
  • 举报
回复
引用 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需要怎么改?
Tiger_Zhao 2016-12-05
  • 打赏
  • 举报
回复
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】 2016-12-05
  • 打赏
  • 举报
回复
引用 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需要怎么改?
道素 2016-12-05
  • 打赏
  • 举报
回复
猜你的需求
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】 2016-12-05
  • 打赏
  • 举报
回复
有人知道吗?大神
【Help】 2016-12-02
  • 打赏
  • 举报
回复
自己顶下,有人会这个存储过程吗?

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧