select * from A表 a join B表 b on a.Field1=(select sum(1) from B表 where Field3<=b.Field3)
->a.Field1=(select sum(1) from B表 where Field3<=b.Field3)这句话起什么作用的啊,能不能解释一下,谢谢先!
--测试--测试数据 create table A表(Field1 int,field2 varchar(10)) insert A表 select 1,'AA' union all select 2,'BB' union all select 3,'CC'create table B表(Field3 varchar(10),field4 int) insert B表 select 'a',44 union all select 'b',55 union all select 'c',66 go--查询 select * from A表 a join B表 b on a.Field1=(select sum(1) from B表 where Field3<=b.Field3) go--删除测试 drop table A表,B表/*--测试结果 Field1 field2 Field3 field4 ----------- ---------- ---------- ----------- 1 AA a 44 2 BB b 55 3 CC c 66(所影响的行数为 3 行) --*/
要求你的field1,field3是主键,并且field1连续
--如果field1不连续,但 field1,field3是主键,可以用:select * from A表 a join B表 b on (select sum(1) from A表 where Field1<=a.Field1) =(select sum(1) from B表 where Field3<=b.Field3)
--如果上述条件不满足,只能用临时表select id=identity(int,1,1),* into #a from A表 select id=identity(int,1,1),* into #b from B表 select Field1,field2,Field3,field4 from #a a join #b b on a.id=b.id
->a.Field1=(select sum(1) from B表 where Field3<=b.Field3)这句话起什么作用的啊,能不能解释一下,谢谢先!
--举个例子 --原表 create table tt1 (name varchar(20),re varchar(20)) create table tt2 (name2 varchar(20),re2 varchar(20)) --用临时表实现 create table #tab1(id int identity(1,1),name varchar(20),re varchar(20)) create table #tab2(id int identity(1,1),name varchar(20),re varchar(20)) insert #tab1 select * from tt1 insert #tab2 select * from tt2 select * from #tab1 full join #tab2 on #tab1.id=#tab2.id drop table #tab1 drop table #tab2
--测试--测试数据 create table a(ida int,a1 int,a2 int) insert a select 1,1,2 insert a select 2,3,4 insert a select 3,5,6create table b(idb int,b1 int,b2 int) insert b select 1,11,12 insert b select 2,13,14 insert b select 3,15,16go--更新 select aa.ida,aa.a1,aa.a2,bb.idb,bb.b1,bb.b2 from a aa,b bb where (select count(ida) from a where ida <= aa.ida ) = (select count(idb) from b where idb <= bb.idb) --显示更新结果 select * from a go--删除测试 drop table a,b
from A表 a join B表 b on a.Field1=(select sum(1) from B表 where Field3<=b.Field3)
create table A表(Field1 int,field2 varchar(10))
insert A表 select 1,'AA'
union all select 2,'BB'
union all select 3,'CC'create table B表(Field3 varchar(10),field4 int)
insert B表 select 'a',44
union all select 'b',55
union all select 'c',66
go--查询
select *
from A表 a join B表 b on a.Field1=(select sum(1) from B表 where Field3<=b.Field3)
go--删除测试
drop table A表,B表/*--测试结果
Field1 field2 Field3 field4
----------- ---------- ---------- -----------
1 AA a 44
2 BB b 55
3 CC c 66(所影响的行数为 3 行)
--*/
from A表 a join B表 b on
(select sum(1) from A表 where Field1<=a.Field1)
=(select sum(1) from B表 where Field3<=b.Field3)
select id=identity(int,1,1),* into #b from B表
select Field1,field2,Field3,field4
from #a a join #b b on a.id=b.id
--原表
create table tt1 (name varchar(20),re varchar(20))
create table tt2 (name2 varchar(20),re2 varchar(20))
--用临时表实现
create table #tab1(id int identity(1,1),name varchar(20),re varchar(20))
create table #tab2(id int identity(1,1),name varchar(20),re varchar(20))
insert #tab1 select * from tt1
insert #tab2 select * from tt2
select * from #tab1 full join #tab2 on #tab1.id=#tab2.id
drop table #tab1
drop table #tab2
--测试--测试数据
create table a(ida int,a1 int,a2 int)
insert a select 1,1,2
insert a select 2,3,4
insert a select 3,5,6create table b(idb int,b1 int,b2 int)
insert b select 1,11,12
insert b select 2,13,14
insert b select 3,15,16go--更新
select aa.ida,aa.a1,aa.a2,bb.idb,bb.b1,bb.b2
from a aa,b bb where (select count(ida) from a where ida <= aa.ida ) = (select count(idb) from b where idb <= bb.idb)
--显示更新结果
select * from a
go--删除测试
drop table a,b