有3个表，如下create table tablea
(
id int primary key identity,
carverA varchar(50),
carverB money,
carverC int
)
insert into tablea(carverA,carverB,carverC) values('test',20,5)
insert into tablea(carverA,carverB,carverC) values('othertest',20,5)create table tableb
(
id int primary key identity,
tablea_id int,--关联tablea表的id
myvalue int,
stopDt datetime
)
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-19 11:11:57')
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-20 10:10:52')create table tablec
(
id int primary key identity,
tableb_id int,--关联tablea表的id
addA varchar(50),
addB money,
addC int,
backA varchar(50),
backB money,
backC int,
createDt datetime default(getdate())
)
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(1,'newtest',25,10,'',0,'')
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(2,'newotherest',35,15,'',0,'')如果tableb的记录中stopDt小于或等于系统当前时间，去tablec表中根据tableb_id和tableb的id关联，查询到
tablec和tableb关联的那条记录，再根据关联tablea的tablea_id去查tablea关联的那条记录，执行如下操作：1、将tablec中的addA更新到tablea的carverA,addB更新到carverB，addC更新到carverC,
2、同时将tableb中的myvalue更新为0，
3、最后再往tablec中插入一条记录，tableb_id=tableb_id backA=addA backB=addB,backC=addc
求教前辈们一个能完成以上操作的语句或存储过程。
(
id int primary key identity,
carverA varchar(50),
carverB money,
carverC int
)
insert into tablea(carverA,carverB,carverC) values('test',20,5)
insert into tablea(carverA,carverB,carverC) values('othertest',20,5)create table tableb
(
id int primary key identity,
tablea_id int,--关联tablea表的id
myvalue int,
stopDt datetime
)
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-19 11:11:57')
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-20 10:10:52')create table tablec
(
id int primary key identity,
tableb_id int,--关联tablea表的id
addA varchar(50),
addB money,
addC int,
backA varchar(50),
backB money,
backC int,
createDt datetime default(getdate())
)
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(1,'newtest',25,10,'',0,'')
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(2,'newotherest',35,15,'',0,'')如果tableb的记录中stopDt小于或等于系统当前时间，去tablec表中根据tableb_id和tableb的id关联，查询到
tablec和tableb关联的那条记录，再根据关联tablea的tablea_id去查tablea关联的那条记录，执行如下操作：1、将tablec中的addA更新到tablea的carverA,addB更新到carverB，addC更新到carverC,
2、同时将tableb中的myvalue更新为0，
3、最后再往tablec中插入一条记录，tableb_id=tableb_id backA=addA backB=addB,backC=addc
求教前辈们一个能完成以上操作的语句或存储过程。
update
tablec
set
adda=a.carverA,
addB=a.carverB,
addC=a.carverC
from
tablea a,table C c
where
a.id=c.tableb_id
update
tableb
set
myvalue=0
tablec
set
adda=a.carverA,
addB=a.carverB,
addC=a.carverC
from
tablea a,table C c
where
a.id=c.tableb_id
update
tableb
set
myvalue=0
into #
from
tableb b join tablec c
on b.id=c.tableb_id
where stopDt<=getdate()
update a
set
carverA=addA,
carverB=addB,
carverC=addC
from
# join tablea a on aid=a.id
join tablec c on cid=c.id--结果：
select * from tablea
id carverA carverB carverC
----------- -------------------------------------------------- --------------------- -----------
1 test 20.00 5
2 newtest 25.00 10update b
set myvalue=0
FROM # join tableb b on bid=b.id--结果：
select * from tableb
id tablea_id myvalue stopDt
----------- ----------- ----------- -----------------------
1 2 0 2009-12-19 11:11:57.000
2 2 37 2009-12-20 10:10:52.000
insert tablec(tableb_id,backA,backB,backC)
select bid,addA,addB,addC
from # join tablec c on cid=c.id--结果：
select * from tablecid tableb_id addA addB addC backA backB backC createDt
----------- ----------- -------------------------------------------------- --------------------- ----------- -------------------------------------------------- --------------------- ----------- -----------------------
1 1 newtest 25.00 10 0.00 0 2009-12-19 11:53:33.590
2 2 newotherest 35.00 15 0.00 0 2009-12-29 11:53:33.590
3 1 NULL NULL NULL newtest 25.00 10 2009-12-19 12:31:53.200drop table #
as
begig
select
b.tablea_id aid,b.id bid,c.id cid
into #
from
tableb b join tablec c
on b.id=c.tableb_id
where stopDt<=getdate()
update a
set
carverA=addA,
carverB=addB,
carverC=addC
from
# join tablea a on aid=a.id
join tablec c on cid=c.id update b
set myvalue=0
FROM # join tableb b on bid=b.id insert tablec(tableb_id,backA,backB,backC)
select bid,addA,addB,addC
from # join tablec c on cid=c.id drop table # end
create table tablea
(
id int primary key identity,
carverA varchar(50),
carverB money,
carverC int
)
insert into tablea(carverA,carverB,carverC) values('test',20,5)
insert into tablea(carverA,carverB,carverC) values('othertest',20,5)create table tableb
(
id int primary key identity,
tablea_id int,--关联tablea表的id
myvalue int,
stopDt datetime
)
insert into tableb(tablea_id,myvalue,stopDt) values(1,37,'2009-12-19 11:11:57')
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-20 10:10:52')create table tablec
(
id int primary key identity,
tableb_id int,--关联tablea表的id
addA varchar(50),
addB money,
addC int,
backA varchar(50),
backB money,
backC int,
createDt datetime default(getdate())
)
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(1,'newtest',25,10,'',0,'')
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(2,'newotherest',35,15,'',0,'')goselect * from tablea
select * from tableb
select * from tablec
/*
id carverA carverB carverC
----------- -------------------------------------------------- --------------------- -----------
1 test 20.0000 5
2 othertest 20.0000 5（所影响的行数为 2 行）id tablea_id myvalue stopDt
----------- ----------- ----------- ------------------------------------------------------
1 1 37 2009-12-19 11:11:57.000
2 2 37 2009-12-20 10:10:52.000（所影响的行数为 2 行）id tableb_id addA addB addC backA backB backC createDt
----------- ----------- -------------------------------------------------- --------------------- ----------- -------------------------------------------------- --------------------- ----------- ------------------------------------------------------
1 1 newtest 25.0000 10 .0000 0 2009-12-19 21:32:15.060
2 2 newotherest 35.0000 15 .0000 0 2009-12-19 21:32:15.060（所影响的行数为 2 行）
*/
--语句如下
update a
set carverA = c.addA,
carverB = c.addB,
carverC = c.addC
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.id
update b
set myvalue = 0
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.idinsert into tablec(tableb_id,addA,addB,addC,backA,backB,backC,createDt)
select c.tableb_id , '',0,0,c.addA,c.addB,c.addC,getdate()
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.idselect * from tablea
select * from tableb
select * from tablec
/*
id carverA carverB carverC
----------- -------------------------------------------------- --------------------- -----------
1 newtest 25.0000 10
2 othertest 20.0000 5（所影响的行数为 2 行）id tablea_id myvalue stopDt
----------- ----------- ----------- ------------------------------------------------------
1 1 0 2009-12-19 11:11:57.000
2 2 37 2009-12-20 10:10:52.000（所影响的行数为 2 行）id tableb_id addA addB addC backA backB backC createDt
----------- ----------- -------------------------------------------------- --------------------- ----------- -------------------------------------------------- --------------------- ----------- ------------------------------------------------------
1 1 newtest 25.0000 10 .0000 0 2009-12-19 21:32:15.060
2 2 newotherest 35.0000 15 .0000 0 2009-12-19 21:32:15.060
3 1 .0000 0 newtest 25.0000 10 2009-12-19 21:32:15.107（所影响的行数为 3 行）
*/drop table tablea , tableb , tablec
create table tablea
(
id int primary key identity,
carverA varchar(50),
carverB money,
carverC int
)
insert into tablea(carverA,carverB,carverC) values('test',20,5)
insert into tablea(carverA,carverB,carverC) values('othertest',20,5)create table tableb
(
id int primary key identity,
tablea_id int,--关联tablea表的id
myvalue int,
stopDt datetime
)
insert into tableb(tablea_id,myvalue,stopDt) values(1,37,'2009-12-19 11:11:57')
insert into tableb(tablea_id,myvalue,stopDt) values(2,37,'2009-12-20 10:10:52')create table tablec
(
id int primary key identity,
tableb_id int,--关联tablea表的id
addA varchar(50),
addB money,
addC int,
backA varchar(50),
backB money,
backC int,
createDt datetime default(getdate())
)
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(1,'newtest',25,10,'',0,'')
insert into tablec(tableb_id,addA,addB,addC,backA,backB,backC) values(2,'newotherest',35,15,'',0,'')goselect * from tablea
select * from tableb
select * from tablec
/*
id carverA carverB carverC
----------- -------------------------------------------------- --------------------- -----------
1 test 20.0000 5
2 othertest 20.0000 5（所影响的行数为 2 行）id tablea_id myvalue stopDt
----------- ----------- ----------- ------------------------------------------------------
1 1 37 2009-12-19 11:11:57.000
2 2 37 2009-12-20 10:10:52.000（所影响的行数为 2 行）id tableb_id addA addB addC backA backB backC createDt
----------- ----------- -------------------------------------------------- --------------------- ----------- -------------------------------------------------- --------------------- ----------- ------------------------------------------------------
1 1 newtest 25.0000 10 .0000 0 2009-12-19 21:32:15.060
2 2 newotherest 35.0000 15 .0000 0 2009-12-19 21:32:15.060（所影响的行数为 2 行）
*/go
--创建存储过程
create proc my_proc as
begin
update a
set carverA = c.addA,
carverB = c.addB,
carverC = c.addC
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.id
update b
set myvalue = 0
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.idinsert into tablec(tableb_id,addA,addB,addC,backA,backB,backC,createDt)
select c.tableb_id , '',0,0,c.addA,c.addB,c.addC,getdate()
from tablea a , tableb b, tablec c
where b.stopDt <= getdate() and b.id = c.id and b.tablea_id = a.id
end
goexec my_procselect * from tablea
select * from tableb
select * from tablec
/*
id carverA carverB carverC
----------- -------------------------------------------------- --------------------- -----------
1 newtest 25.0000 10
2 othertest 20.0000 5（所影响的行数为 2 行）id tablea_id myvalue stopDt
----------- ----------- ----------- ------------------------------------------------------
1 1 0 2009-12-19 11:11:57.000
2 2 37 2009-12-20 10:10:52.000（所影响的行数为 2 行）id tableb_id addA addB addC backA backB backC createDt
----------- ----------- -------------------------------------------------- --------------------- ----------- -------------------------------------------------- --------------------- ----------- ------------------------------------------------------
1 1 newtest 25.0000 10 .0000 0 2009-12-19 21:32:15.060
2 2 newotherest 35.0000 15 .0000 0 2009-12-19 21:32:15.060
3 1 .0000 0 newtest 25.0000 10 2009-12-19 21:32:15.107（所影响的行数为 3 行）
*/drop table tablea , tableb , tablec
drop proc my_proc
标注那个红色条件感觉不妥，不能这样判断的