insert into A(no,icount)
select * from B where exists(select 1 from (
select table1.id,table1.no,table1.icount icount1,table2.icount icount2 from
(
select top 100 percent id=(select count(*) from A b where flag=1 and a.no=b.no and a.icount>=b.icount),* from A a
where flag=1
order by no,icount
)table1 inner join
(
select top 100 percent id=(select count(*) from A b where flag=-1 and a.no=b.no and a.icount>=b.icount),* from A a
where flag=-1
order by no,icount)table2
on table1.id=table2.id
)table3 where B.no=table3.no and B.icount between icount1 and icount2)
select * from B where exists(select 1 from (
select table1.id,table1.no,table1.icount icount1,table2.icount icount2 from
(
select top 100 percent id=(select count(*) from A b where flag=1 and a.no=b.no and a.icount>=b.icount),* from A a
where flag=1
order by no,icount
)table1 inner join
(
select top 100 percent id=(select count(*) from A b where flag=-1 and a.no=b.no and a.icount>=b.icount),* from A a
where flag=-1
order by no,icount)table2
on table1.id=table2.id
)table3 where B.no=table3.no and B.icount between icount1 and icount2)
insert into @A select 1,7 ,1
insert into @A select 1,10,-1
insert into @A select 1,20,1
insert into @A select 1,25,-1
insert into @A select 1,25,1
insert into @A select 1,30,-1
insert into @A select 1,30,1declare @B table(no int,icount int)
insert into @B select 1,11
insert into @B select 1,18
insert into @B select 1,23insert into @A(no,icount)
select b.* from @A a,@B b
where
a.flag=1
and
b.icount between a.icount and (select min(icount) from @A where icount>a.icount and flag=-1)select * from @A order by icount,flag/*
no icount flag
----------- ----------- -----------
1 7 1
1 10 -1
1 20 1
1 23 NULL
1 25 -1
1 25 1
1 30 -1
1 30 1
*/
我水平有限,看不太懂,而且你写的太复杂了吧,如果A表和B表各有百万以上的数据,不知道按你的写法要跑多久啊