大家好，请教一个问题
我现在有3张表，结构如下

A 表
type pid name
B 1 aa
B 2 bb
C 1 cc


B
id phoneB
1 010-12345678
2 011-12345678

C
id phoneC
1 012-12345678

现在想要输出的结果是
name phone
aa 010-12345678
bb 011-12345678
cc 012-12345678

数据库是sql server r2的



[解决办法]

SQL code

select a.name,b.phone from a join b on a.pid=b.id and a.type='b'union allselect a.name,c.phone from a join b on a.pid=c.id and a.type='c'
[解决办法]
SQL code
select  a.name,b.phonefrom  a,  (select id,phoneb as phone from b union all select id,phonec as phone from c)bwhere  a.id=b.id
[解决办法]
SQL code
SELECT A.NAME,B.PHONEB AS PHONEFROM A INNER JOIN B ON A.PID = B.ID WHERE A.TYPE = 'B'UNION ALLSELECT A.NAME,C.PHONEC FROM A INNER JOIN C ON A.PID = C.IDWHERE A.TYPE = 'C'
[解决办法]
SQL code
select a.name,b.phone from a join b on a.pid=b.id and a.type='b'union allselect a.name,c.phone from a join b on a.pid=c.id and a.type='c'
[解决办法]
SQL code
select a.name,b.phonefrom ajoin(select 'b' as type,id,phoneb as phone from b union all select 'c' as type,id,phonec as phone from c) bon a.type=b.type and a.pid=b.id
[解决办法]
SQL code
------------------------------ Author  :fredrickhu(小F，向高手学习)-- Date    :2011-11-10 13:47:55-- Version:--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) --    Apr 22 2011 11:57:00 --    Copyright (c) Microsoft Corporation--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[A]if object_id('[A]') is not null drop table [A]go create table [A]([type] varchar(1),[pid] int,[name] varchar(2))insert [A]select 'B',1,'aa' union allselect 'B',2,'bb' union allselect 'C',1,'cc'--> 测试数据：[B]if object_id('[B]') is not null drop table [B]go create table [B]([id] int,[phoneB] varchar(12))insert [B]select 1,'010-12345678' union allselect 2,'011-12345678'--> 测试数据：[C]if object_id('[C]') is not null drop table [C]go create table [C]([id] int,[phoneC] varchar(12))insert [C]select 1,'012-12345678'--------------开始查询--------------------------select   a.name,b.phonefrom  (select px=row_number()over(order by getdate()),* from a)a,  (select px=row_number()over(order by getdate()),* from(select id,phoneb as phone from b union all select id,phonec as phone from c)t)bwhere  a.px=b.px----------------结果----------------------------/* name phone---- ------------aa   010-12345678bb   011-12345678cc   012-12345678(3 行受影响)*/