/*
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
*/
转载于:https://www.cnblogs.com/zengxiangzhan/archive/2009/12/05/1617458.html