优化求解：上万条带参数的查询语句
手头的一个活，可以简化为以下模型：
declare @i int, @j int, @iCount int
set @i = 0
set @j = 0
while(@i < 100)
begin
while(@j < 100)
begin
set @iCount = (select count(*) from table1 where field1 = @i and field2 = @j)
if (@iCount <> 0)
begin
insert into table2(field1, field2) values(@i, @j)
end
set @j = @j +1
end
set @i = @i +1
end
针对以上模型，求最佳效率的解决方案，比如通过临时表、批量执行或把多条select语句合成一条语句等等，诸位以为如何？
[解决办法]
--try
declare @i int, @j int, @iCount int
set @i = 0
set @j = 0
while(@i < 100)
begin
while(@j < 100)
begin
insert into table2(field1, field2)
select @i, @j
where exists(select 1 from table1 where field1 = @i and field2 = @j)
set @j = @j +1
end
set @i = @i +1
end
[解决办法]
insert into table2(field1, field2)
select field1,field2 from table1
WHERE field1 <100 AND field2 <100 GROUP BY field1,field2 HAVING count(1)> 0
[解决办法]
楼上正解。
