/*
a表 a.user_id a.name
b表 b.user_id b.name
c表 c.user_id c.user_type
当c表的 c.user_type = "a" 时 它显示 a表的 a.name
当c表的 c.user_type = "b" 时 它显示 b表的 b.name
a,b,c表中的 a.user_id = c.user_id,b.user_id = c.user_id
*/
if object_id ( ' ta ' ) is not null drop table ta
go
create TABLE ta( [ user_ID ] int ,name varchar ( 50 ))
INSERT INTO ta select
1 , ' a1 ' union all select
2 , ' a2 '
if object_id ( ' tb ' ) is not null drop table tb
go
create TABLE tb( [ user_ID ] int ,name varchar ( 50 ))
INSERT INTO tb select
1 , ' b1 ' union all select
2 , ' b2 '
if object_id ( ' tc ' ) is not null drop table tc
go
create TABLE tc( [ user_ID ] int ,user_type varchar ( 50 ))
INSERT INTO tc select
1 , ' a ' union all select
2 , ' b '
select * from ta
select * from tb
select * from tc
-- 方法一
select c. [ user_id ] ,name = case user_type when ' a ' then a.name when ' b ' then b.name end
from tc c,ta a,tb b
where a. [ user_id ] = c. [ user_id ] and b. [ user_id ] = c. [ user_id ]
-- 方法二
SELECT tc. user_ID , CASE user_type WHEN ' a ' THEN ta.name WHEN ' b ' THEN tb.name END AS 输出
FROM tc INNER JOIN
ta ON tc. user_ID = ta. user_ID INNER JOIN
tb ON tc. user_ID = tb. user_ID
/*
user_ID name
----------- --------------------------------------------------
1 a1
2 a2
user_ID name
----------- --------------------------------------------------
1 b1
2 b2
user_ID user_type
----------- --------------------------------------------------
1 a
2 b
user_id 输出
----------- --------------------------------------------------
1 a1
2 b2
a表 a.user_id a.name
b表 b.user_id b.name
c表 c.user_id c.user_type
当c表的 c.user_type = "a" 时 它显示 a表的 a.name
当c表的 c.user_type = "b" 时 它显示 b表的 b.name
a,b,c表中的 a.user_id = c.user_id,b.user_id = c.user_id
*/
if object_id ( ' ta ' ) is not null drop table ta
go
create TABLE ta( [ user_ID ] int ,name varchar ( 50 ))
INSERT INTO ta select
1 , ' a1 ' union all select
2 , ' a2 '
if object_id ( ' tb ' ) is not null drop table tb
go
create TABLE tb( [ user_ID ] int ,name varchar ( 50 ))
INSERT INTO tb select
1 , ' b1 ' union all select
2 , ' b2 '
if object_id ( ' tc ' ) is not null drop table tc
go
create TABLE tc( [ user_ID ] int ,user_type varchar ( 50 ))
INSERT INTO tc select
1 , ' a ' union all select
2 , ' b '
select * from ta
select * from tb
select * from tc
-- 方法一
select c. [ user_id ] ,name = case user_type when ' a ' then a.name when ' b ' then b.name end
from tc c,ta a,tb b
where a. [ user_id ] = c. [ user_id ] and b. [ user_id ] = c. [ user_id ]
-- 方法二
SELECT tc. user_ID , CASE user_type WHEN ' a ' THEN ta.name WHEN ' b ' THEN tb.name END AS 输出
FROM tc INNER JOIN
ta ON tc. user_ID = ta. user_ID INNER JOIN
tb ON tc. user_ID = tb. user_ID
/*
user_ID name
----------- --------------------------------------------------
1 a1
2 a2
user_ID name
----------- --------------------------------------------------
1 b1
2 b2
user_ID user_type
----------- --------------------------------------------------
1 a
2 b
user_id 输出
----------- --------------------------------------------------
1 a1
2 b2
*/
本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2009/12/05/1617458.html，如需转载请自行联系原作者
