select a.*,b.g,b.h,b.i,b.j,b.k from ta,tb where ta.a = tb.a
select a.*,b.g,b.h,b.i,b.j,b.k from ta a,tb b where a.a = b.a
当然笨办法是:select A.a,A.b,A.c,A.d,A.e,A.f ,B.g,B.h,B.i,B.j,B.k from A,B where A.a=B.a 如果字段很多的话,这样写肯定是麻烦的,我想要的是简单的select 语句,实现所选择的记录集中不含相同的字段。
create table ta(id int,col1 int,col2 int) create table tb(id int,col3 int,col4 int) godeclare @s varchar(8000) select @s=isnull(@s,'') + 'a.'+name +',' from syscolumns where id = object_id('ta') order by colidselect @s=isnull(@s,'') + 'b.'+name +',' from syscolumns where id = object_id('tb') and colid <> 1 order by colidselect @s ='select '+ stuff(@s,len(@s),1,'') + ' from ta a,tb b where a.id = b.id' exec(@s) /* id col1 col2 col3 col4 ----------- ----------- ----------- ----------- ----------- */drop table ta,tb
create table ta(a int,b int,c int,d int,e int,f int ) create table tb(a int,g int,h int,i int,j int,k int) godeclare @s varchar(8000) select @s=isnull(@s,'') + 'a.'+name +',' from syscolumns where id = object_id('ta') order by colidselect @s=isnull(@s,'') + 'b.'+name +',' from syscolumns where id = object_id('tb') and colid <> 1 order by colidselect @s ='select '+ stuff(@s,len(@s),1,'') + ' from ta a,tb b where a.a = b.a' exec(@s) /* a b c d e f g h i j k ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- */drop table ta,tb
create table A(a int,b int,c int,d int,e int,f int) insert into A select 1,2,3,4,5,6create table B(a int,g int,h int,i int,j int,k int) insert into B select 1,7,8,9,10,11godeclare @s varchar(100)select @s=isnull(@s,'')+',a.'+name from syscolumns where id=object_id('A') select @s=isnull(@s,'')+',b.'+name from syscolumns where id=object_id('B') and name<>'a'set @s=stuff(@s,1,1,'')set @s='select '+@s+' from A,B where A.a=B.a'--print @s/* select a.a,a.b,a.c,a.d,a.e,a.f,b.g,b.h,b.i,b.j,b.k from A,B where A.a=B.a */ exec (@s)drop table a,b
create table A(a int,b int,c int,d int,e int,f int) insert into A select 1,2,3,4,5,6create table B(a int,g int,h int,i int,j int,k int) insert into B select 1,7,8,9,10,11godeclare @s varchar(100)select @s=isnull(@s,'')+',a.'+name from syscolumns where id=object_id('A') select @s=isnull(@s,'')+',b.'+name from syscolumns where id=object_id('B') and name<>'a'set @s=stuff(@s,1,1,'')set @s='select '+@s+' from A,B where A.a=B.a'--print @s/* select a.a,a.b,a.c,a.d,a.e,a.f,b.g,b.h,b.i,b.j,b.k from A,B where A.a=B.a */ exec (@s)/* a b c d e f g h i j k ----------------------------------------------------------------------------------------- 1 2 3 4 5 6 7 8 9 10 11 */drop table a,b
from ta,tb
where ta.a = tb.a
from ta a,tb b
where a.a = b.a
当然笨办法是:select A.a,A.b,A.c,A.d,A.e,A.f ,B.g,B.h,B.i,B.j,B.k from A,B where A.a=B.a
如果字段很多的话,这样写肯定是麻烦的,我想要的是简单的select 语句,实现所选择的记录集中不含相同的字段。
create table tb(id int,col3 int,col4 int)
godeclare @s varchar(8000)
select @s=isnull(@s,'') + 'a.'+name +','
from syscolumns
where id = object_id('ta') order by colidselect @s=isnull(@s,'') + 'b.'+name +','
from syscolumns
where id = object_id('tb') and colid <> 1
order by colidselect @s ='select '+ stuff(@s,len(@s),1,'') + ' from ta a,tb b where a.id = b.id'
exec(@s)
/*
id col1 col2 col3 col4
----------- ----------- ----------- ----------- -----------
*/drop table ta,tb
create table tb(a int,g int,h int,i int,j int,k int)
godeclare @s varchar(8000)
select @s=isnull(@s,'') + 'a.'+name +','
from syscolumns
where id = object_id('ta') order by colidselect @s=isnull(@s,'') + 'b.'+name +','
from syscolumns
where id = object_id('tb') and colid <> 1
order by colidselect @s ='select '+ stuff(@s,len(@s),1,'') + ' from ta a,tb b where a.a = b.a'
exec(@s)
/*
a b c d e f g h i j k
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
*/drop table ta,tb
insert into A
select 1,2,3,4,5,6create table B(a int,g int,h int,i int,j int,k int)
insert into B
select 1,7,8,9,10,11godeclare @s varchar(100)select @s=isnull(@s,'')+',a.'+name from syscolumns where id=object_id('A')
select @s=isnull(@s,'')+',b.'+name from syscolumns where id=object_id('B') and name<>'a'set @s=stuff(@s,1,1,'')set @s='select '+@s+' from A,B where A.a=B.a'--print @s/*
select a.a,a.b,a.c,a.d,a.e,a.f,b.g,b.h,b.i,b.j,b.k from A,B where A.a=B.a
*/
exec (@s)drop table a,b
insert into A
select 1,2,3,4,5,6create table B(a int,g int,h int,i int,j int,k int)
insert into B
select 1,7,8,9,10,11godeclare @s varchar(100)select @s=isnull(@s,'')+',a.'+name from syscolumns where id=object_id('A')
select @s=isnull(@s,'')+',b.'+name from syscolumns where id=object_id('B') and name<>'a'set @s=stuff(@s,1,1,'')set @s='select '+@s+' from A,B where A.a=B.a'--print @s/*
select a.a,a.b,a.c,a.d,a.e,a.f,b.g,b.h,b.i,b.j,b.k from A,B where A.a=B.a
*/
exec (@s)/*
a b c d e f g h i j k
-----------------------------------------------------------------------------------------
1 2 3 4 5 6 7 8 9 10 11
*/drop table a,b