sql server update+select(子查询修改)20190304
if OBJECT_ID('tempdb..##t2') is not null drop table ##t2;
create table ##t2
(
a int,
b int,
c datetime,
d varchar(100),
e varchar(100),
f int,
g int
);
select * from ##t2;
update ##t2
set f = t3.f,
g = t3.g
FROM ##t2,(select f.NID,count(s.NID) skuCount,sum(s.Qty) skuSum
from table1(nolock) t1,table2(nolock) t2
where f.NID = s.nid
group by f.NID) t3
where ##t2.b = t3.NID;
go
--TestDemo if OBJECT_ID('tempdb..##t1') is not null drop table ##t1; create table ##t1 ( id int ); if OBJECT_ID('tempdb..##t2') is not null drop table ##t2; create table ##t2 ( id int, id2 int, quantity int ); if OBJECT_ID('tempdb..##t3') is not null drop table ##t3; create table ##t3 ( id int, id3 int, quantity2 int ); insert into ##t1 values(1); insert into ##t1 values(2); insert into ##t1 values(3); insert into ##t1 values(4); insert into ##t1 values(5); go insert into ##t2 values(1,1,1); insert into ##t2 values(2,1,2); insert into ##t2 values(3,1,3); insert into ##t2 values(4,2,4); insert into ##t2 values(5,2,5); go insert into ##t3 values(1,1,6); insert into ##t3 values(2,1,7); insert into ##t3 values(3,2,8); insert into ##t3 values(4,2,9); insert into ##t3 values(5,2,10); go if OBJECT_ID('tempdb..##t4') is not null drop table ##t4; create table ##t4 ( id1 int, id2 int, quantityCount int, quantitySum int ); go insert into ##t4(id1,id2) select t1.id,t2.id from ##t1 t1,##t2 t2 where t1.id = t2.id2; update ##t4 set quantityCount = t1.quantityCount, quantitySum = t1.quantitySum FROM ##t1,(select t2.id, count(t3.id) quantityCount,sum(t3.quantity2) quantitySum from ##t2 t2,##t3 t3 where t2.id = t3.id3 group by t2.id) t1 where ##t4.id2 = t1.id; go select * from ##t4;
go
2015年10月-2023年6月 总计7年.
所有总计:7年.
我是唯物唯心二元论的,暂时主学法语.
学习打卡交流QQ群:946556683
所有总计:7年.
我是唯物唯心二元论的,暂时主学法语.
学习打卡交流QQ群:946556683