select
a.id,
make = max(case when a.make = b.id then a.name end),
design = max(case when a.design = b.id then a.name end),
use = max(case when a.use = b.id then a.name end)
from
设备表 a,
单位表 b
group by
a.id
a.id,
make = max(case when a.make = b.id then a.name end),
design = max(case when a.design = b.id then a.name end),
use = max(case when a.use = b.id then a.name end)
from
设备表 a,
单位表 b
group by
a.id
insert into 单位表 select 1,'IBM ',1
insert into 单位表 select 2,'MS ',2
insert into 单位表 select 3,'LENOVO',3
insert into 单位表 select 4,'NEC ',2create table 设备表(id int,make int,design int,[use] int)
insert into 设备表 select 1,2,1,3
insert into 设备表 select 2,4,1,3select
a.id,
make = max(case when a.make = b.id then b.name end),
design = max(case when a.design = b.id then b.name end),
[use] = max(case when a.[use] = b.id then b.name end)
from
设备表 a,
单位表 b
group by
a.id
from 设备表 a,单位表 b,单位表 c,单位表 d
where a.make=b.type and a.design=c.type and a.[use]=d.type
create table tc(id int,t1 int,t2 int,t3 int)
insert into tn select 1,'IBM',1 union all select 2,'MS',2 union all select 3,'LENOVO',3 union all select 4,'NEC',2
insert into tc select 1,2,1,3 union all select 2,4,1,3
go
create function f_getn(@id int,@type int)
returns nvarchar(10)
as
begin
declare @s nvarchar(10)
select @s=n from tn where id=@id and type=@type
return(@s)
end
goselect id,dbo.f_getn(t1,2) as t1,dbo.f_getn(t2,1) as t2,dbo.f_getn(t3,3) as t3 from tcdrop function f_getn
drop table tn
drop table tc