update test1 set je = jj * 人口数
人口数 根据 left(bh,4) 获得

create table test1 (bh nvarchar(20),jj int,je int)

insert test1 ('1101001',10,0)
insert test1 ('1101001',10,0)
insert test1 ('1101001',10,0)
insert test1 ('1102001',10,0)
insert test1 ('1102001',10,0)
insert test1 ('1102001',10,0)
insert test1 ('1102001',10,0)
insert test1 ('1102001',10,0)
insert test1 ('1103001',10,0)
insert test1 ('1103001',10,0)

我的做法
：


declare @min int,@max int,@num int
select @min=min(left(bh,4)),@max=max(left(bh,4)) from test1

while @min<=@max
begin
select @num=count(*) from test1 where left(bh,4) = @min
print @num
update test1
set je = jj * @num where left(bh,4) = @min

set @min=@min + 1
end

由于编号可能不连续，我写的这个执行速度特别慢，谁有更好的？

update test1  set je = jj* a.total  from (select left(bh,4) bh4, count(bh) total from test1 group by left(bh,4)) a  where left(test1.bh,4)=a.bh4

update test1 set je = jj* left(bh,4)
不可以么？