求助！！！多表查询问题
有三表如下：
A表
name record_A
张三 1210
李四 4060
高骏 005090
张三 1210
张三 1210
王民 1077
王大学 104090
李四 4060
陈小华 1210
李大华 1210
张三 1210
李大华 1210
林大三 53127
黄晓华 446024
马宁 7740
B表
name record_B
陈小华 1210
农家乐 l403072
陈小华 749024
张三 1210
李华 4024
张三三 1210104
张三 121023
张三 1210
C表
name record_C
农林 4044
黄向东 442740
张三 1210
周宝 7730
张三 1210
张三三 1210
李宏 4421
陈中国 1210
张三三 1210
张三 43201
欲通过A、B、C表的字段record_A、record_B、record_C查询某一代码，把查询到三表的name字段记录输出，要求消除相同行，另加上三个BS_A、BS_B、BS_C标识字段,标上各表的代码，如A表标识为'A',B表标识为'B',C表标识为'C'。如查询三表record_A、record_B、record_C字段的值等于1210时，三表相同部分，如下：
A表
name record_A
张三 1210
张三 1210
张三 1210
陈小华 1210
李大华 1210
张三 1210
李大华 1210
B表
name record_B
陈小华 1210
张三 1210
张三 1210
C表
name record_C
张三 1210
张三 1210
张三三 1210
张三三 1210
要求输出结果集如下：
name BS_A BS_B BS_C
张三 A B C /*张三在A、B、C表有记录，分别标上A、B、C。*/
陈小华 A B /*陈小华在A、B表有记录，分别标上A、B。*/
李大华 A /*李大华在A表有记录，标上A。*/
张三三 C /*张三三在C表有记录，标上C。*/
要得到这样结果集，过程语句如何写？谢谢！！！
问题点数：80、回复次数：10Top
1 楼lsqkeke（可可）回复于 2006-03-03 13:43:59 得分 0
select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'A' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'A' else ''end)
from (select distinct [name],record_A from A where record_A='1210')tTop
2 楼lsqkeke（可可）回复于 2006-03-03 13:44:26 得分 0
select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (select distinct [name],record_A from A where record_A='1210')t
Top
3 楼wgsasd311（自强不息）回复于 2006-03-03 13:50:13 得分 0
select name,
BS_A=max(case when a.name is null then '' else a.name end),
BS_B=max(case when b.name is null then '' else b.name end),
BS_C=max(case when c.name is null then '' else c.name end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by nameTop
4 楼lsqkeke（可可）回复于 2006-03-03 13:52:13 得分 70
哦 还有点小细节要处理进去
改一下：
select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
Top
5 楼wgsasd311（自强不息）回复于 2006-03-03 13:52:26 得分 0
select [name]=case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by nameTop
6 楼zhouhaihe（）回复于 2006-03-03 13:53:49 得分 8
select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from
(select distinct [name],record_A from A where record_A='1210'
union select distinct [name],record_B from B where record_B='1210'
union select distinct [name],record_C from C where record_C='1210')tTop
7 楼wgsasd311（自强不息）回复于 2006-03-03 13:53:53 得分 2
select [name]=case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by case when a.name is not null then a.name
when b.name is not null then b.name
else c.name endTop
8 楼zcxc（知春小草）回复于 2006-03-03 16:19:51 得分 0
都不行啊！！！
to: lsqkeke(可可) ( ) 信誉：100
select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
使用了union
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
会提示
列名'record_B'、'record_C'无效
to: wgsasd311(自强不息) ( ) 信誉：100
select [name]=case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end
会得到这样结果集：
name BS_A BS_B BS_C
-------------------- ---- ---- ----
张三三 A B C
陈小华 A B C
李大华 A B C
张三 A B C
(4 row(s) affected)
Top
9 楼zcxc（知春小草）回复于 2006-03-03 16:51:59 得分 0
不好意思，是我搞错了，lsqkeke(可可) ( ) 的方法是可行的。谢谢大家帮助。Top
10 楼zcxc（知春小草）回复于 2006-03-04 14:51:04 得分 0
lsqkeke(可可)兄的方法可以达到上述要求，但要查询多达十几个表时，查询效率有些低，响应速度有些慢，有没有更好的方法？用join方法效果如何？请大家给点方法或思路。Top
