join on 结果集1.number1=ascii(结果集2.char1)-36join 是 SQL 的基本内容。连“union”都懂,不应该不懂“join”。
唉,写全了吧!select 结果集1.number1,结果集2.char1 from 结果集1 full join 结果集2 on 结果集1.number1=ascii(结果集2.char1)-36
to w_rose(w_rose) : 不是这个样子,因为那只是个简单的样例, “结果集1.number1=ascii(结果集2.char1)-36”,这里 int 和 char 只是举例的类型,事实上两个结果集的字段类型可能根本没有联系。 如果要使用jion的话,那联结条件应当是两个结果集的每一行按照相同的记录号结合。因为SQL SERVER 中没有记录号(rownum)这一概念,所以实现起来有点棘手。 当然,考虑到效率问题,也不应当使用临时表。
create table #a(id int identity(1,1),num1 int) insert into #a(num1) select number1 from table1 create table #b(id int identity(1,1),chr1 varchar(10)) insert into #b(chr1) select char1 from char1 select #a.num1 as number1,#b.chr1 as char1 from #a,#b where #a.id=#b.id
declare @a table(id int identity primary key,number1 int) insert into @a(number1) select number1 from table1 declare @b table(id int identity primary key,char1 varchar(10)) insert into @b(char1) select char1 from char1 select a.number1,b.char1 from @a as a full join @b on a.id=b.id
declare @a table(id int identity primary key,number1 int) insert into @a(number1) select number1 from table1 declare @b table(id int identity primary key,char1 varchar(10)) insert into @b(char1) select char1 from table2 select a.number1,b.char1 from @a as a full join @b as b on a.id=b.id 其实,使用临时表(#a,#b)也还是很好的,只不过要记得drop。
to: w_rose(w_rose) 嘿嘿,老实说,从效率上说用表变量和临时表差的并不是太多,只是它不用硬性存储。 不过主要是老板的意见~~:)
from 结果集1 full join 结果集2 on 结果集1.number1=ascii(结果集2.char1)-36
不是这个样子,因为那只是个简单的样例,
“结果集1.number1=ascii(结果集2.char1)-36”,这里 int 和 char 只是举例的类型,事实上两个结果集的字段类型可能根本没有联系。
如果要使用jion的话,那联结条件应当是两个结果集的每一行按照相同的记录号结合。因为SQL SERVER 中没有记录号(rownum)这一概念,所以实现起来有点棘手。
当然,考虑到效率问题,也不应当使用临时表。
insert into #a(num1) select number1 from table1
create table #b(id int identity(1,1),chr1 varchar(10))
insert into #b(chr1) select char1 from char1
select #a.num1 as number1,#b.chr1 as char1 from #a,#b where #a.id=#b.id
insert into @a(number1) select number1 from table1
declare @b table(id int identity primary key,char1 varchar(10))
insert into @b(char1) select char1 from char1
select a.number1,b.char1 from @a as a full join @b on a.id=b.id
insert into @a(number1) select number1 from table1
declare @b table(id int identity primary key,char1 varchar(10))
insert into @b(char1) select char1 from table2
select a.number1,b.char1 from @a as a full join @b as b on a.id=b.id
其实,使用临时表(#a,#b)也还是很好的,只不过要记得drop。
嘿嘿,老实说,从效率上说用表变量和临时表差的并不是太多,只是它不用硬性存储。
不过主要是老板的意见~~:)