SQL 数据库有两种表
表A字段名为:A1,A2,A3
对应的记录值为:3,2,6
表B的字段名为:B1,B2
对应的记录值为:A1,东
A2,南
A3,西
就是说表B是对表A的字段名的一个解释,如何用一个查询语句得到如下记录集:
A1,东,3
A2,南,2
A3,西,6
就是每条记录既能显示A的值,也能出现A的对应解释,如何实现?
表A字段名为:A1,A2,A3
对应的记录值为:3,2,6
表B的字段名为:B1,B2
对应的记录值为:A1,东
A2,南
A3,西
就是说表B是对表A的字段名的一个解释,如何用一个查询语句得到如下记录集:
A1,东,3
A2,南,2
A3,西,6
就是每条记录既能显示A的值,也能出现A的对应解释,如何实现?
insert into @A
select 3,2,6declare @B table(B1 CHAR(2),B2 nchar(1))
insert into @B
select 'A1','东' union all
select 'A2','南' union all
select 'A3','西'select *,case when B1 = 'A1' then (select A1 from @A )
when B1 = 'A2' then (select A2 from @A )
when B1 = 'A3' then (select A3 from @A )
else null end as A
from @b/*
(1 行受影响)(3 行受影响)
B1 B2 A
---- ---- -----------
A1 东 3
A2 南 2
A3 西 6(3 行受影响)*/
select name from sysobject where id = object_id("A"),select * from B
aa.name,bb.b1,bb.b2
FROM
(select name from sysobject where id = object_id("A")) as aa,
(select * from B) as bb
WHERE
aa.name = bb.b1
select B.b1,B.b2,
(select case when B1 = 'A1' then (select A1 from A )
when B1 = 'A2' then (select A2 from A )
when B1 = 'A3' then (select A3 from A )
else null
end
from B
group by B1)