字段替换的三种实现

要求把表b的数字改成表a对应id的name
if object_id('a') is not null
drop table a
go
create table a( id int,name varchar(10))
insert into a select 1,'a'
insert into a select 2,'b'
insert into a select 3,'c'
insert into a select 4,'d'
go
if object_id('b') is not null
drop table b
go
create table b(id1 int,id2 int)
insert into b select 1,2
insert into b select 3,4
go

select * from a
select * from b

create function dbo.fn(@id int)
returns nvarchar(10)
as
begin
declare @name nvarchar(10)
select @name=name from a where id=@id
return @name
end
go

select dbo.fn(id1) as name1,dbo.fn(id2) as name2 from b
Result
name1 name2
---------- ----------
a b
c d

（所影响的行数为 2 行）

select (select name from a where id=id1) as name1,(select name from a where id=id2) as name2 from b
Result
name1 name2
---------- ----------
a b
c d

（所影响的行数为 2 行）
select a1.name as name1,a2.name as name2 from a a1,a a2,b where a1.id=b.id1 and a2.id=b.id2
Result
name1 name2
---------- ----------
a b
c d

（所影响的行数为 2 行）


drop table a
go
drop tabl b

[此贴子已经被作者于2007-4-2 13:52:12编辑过]