TB1id pid shuliang name
1 23 60 xx
2 23 30 yy
3 23 10 zz
4 24 3 cc
5 78 34 ddTB1中pid为23的总数为100,分三段数量,分别对应xx,yy,zzTB2id pid name
1 23
2 23
30 23
45 23
~
~
TB2中PID为23的总数为100个和TB1中PID的总量相等,怎么样批量更新TB2的name为TB1中对应的NAME要按数量分段更新
比如更新后,TB2结果为:pid=23的前60个数为xx,其次30个为YY,后面10个为zz注意:TB2中的ID不是连续的
1 23 60 xx
2 23 30 yy
3 23 10 zz
4 24 3 cc
5 78 34 ddTB1中pid为23的总数为100,分三段数量,分别对应xx,yy,zzTB2id pid name
1 23
2 23
30 23
45 23
~
~
TB2中PID为23的总数为100个和TB1中PID的总量相等,怎么样批量更新TB2的name为TB1中对应的NAME要按数量分段更新
比如更新后,TB2结果为:pid=23的前60个数为xx,其次30个为YY,后面10个为zz注意:TB2中的ID不是连续的
CREATE TABLE #temp(id int, pid int, shuliang int, name varchar(100))
insert #temp
select '1','23','60','xx' union all
select '2','23','30','yy' union all
select '3','23','10','zz' union all
select '4','24','3','cc' union all
select '5','78','34','dd' if OBJECT_ID('tempdb..#temp2', 'u') is not null drop table #temp2;
CREATE TABLE #temp2(id int, pid int, name varchar(100))
insert #temp2
select '1','23', null union all
select '2','23', null union all
select '30','23', null union all
select '45','23', null --sql:
;WITH cte AS
(
SELECT x.pid, x.name, y.rowid, updatename=y.name FROM
(
SELECT pid, name,
beginshuliang=ISNULL((SELECT SUM(shuliang)+1 FROM #temp a WHERE a.pid=t.pid AND a.id<t.id), 1),
endshuliang =(SELECT SUM(shuliang) FROM #temp a WHERE a.pid=t.pid AND a.id<=t.id)
FROM #temp t --如果这个表不是很大,这里的效率就可以
) x
INNER JOIN
(
SELECT rowid = ROW_NUMBER() OVER(PARTITION BY pid ORDER BY id), * FROM #temp2 --为pid, id字段建立复合索引.如果没有条件,这里全表扫,没效率
) y
ON x.pid = y.pid
AND y.rowid BETWEEN x.beginshuliang AND x.endshuliang --JOIN效率不是很好
)
UPDATE cte
SET updatename = name--result:
SELECT * FROM #temp2
/*
id pid name
1 23 xx
2 23 xx
30 23 xx
45 23 xx
*/
比如说当PID=23时,批量更新TB2的name为TB1中相对应的数量NAME这个如何处理?
SET @pid = 123;WITH cte AS
(
SELECT x.pid, x.name, y.rowid, updatename=y.name FROM
(
SELECT pid, name,
beginshuliang=ISNULL((SELECT SUM(shuliang)+1 FROM #temp a WHERE a.pid=t.pid AND a.id<t.id), 1),
endshuliang =(SELECT SUM(shuliang) FROM #temp a WHERE a.pid=t.pid AND a.id<=t.id)
FROM #temp t
WHERE pid = @pid --pid字段加索引
) x
INNER JOIN
(
SELECT rowid = ROW_NUMBER() OVER(PARTITION BY pid ORDER BY id), * FROM #temp2 WHERE pid = @pid --为pid, id字段建立复合索引
) y
ON x.pid = y.pid
AND y.rowid BETWEEN x.beginshuliang AND x.endshuliang
)
UPDATE cte
SET updatename = name