select * from ta a left join b on a.id=b.id and a.type=1 union all select * from ta a left join c on a.id=c.id and a.type=2
select * from a left join b on .... where a.type=1 union all select * from a left join c on .... where a.type=2
select a.id, isnull(b.c1,c.c1),isnull(b.c2,c.c2),.... from ta a left join tb b on a.id = b.id and a.type = 1 left join tc c on a.id = c.id and a.type = 2
select * from a left inner join b on a.type=1 and 连接条件 union all select * from a left inner join c on a.type=2 and 连接条件
--创建环境 create table ta ( id int, type int )create table tb ( id int, name varchar(10) )create table tc ( id int, name varchar(10) )insert into ta select 1,1 insert into ta select 2,1 insert into ta select 3,2 insert into ta select 4,2insert into tb select 1,'张三' insert into tb select 2,'李四' insert into tc select 3,'王二麻子' insert into tc select 4,'冷箫轻笛'--查询 select a.id,case a.type when 1 then b.name when 2 then c.name end as name from ta a left join tb b on a.id = b.id and a.type = 1 left join tc c on a.id = c.id and a.type = 2--结果 /* id name ----------- ---------- 1 张三 2 李四 3 王二麻子 4 冷箫轻笛(4 行受影响)*/--删除环境 drop table ta drop table tb drop table tc
--查询 select a.id,isnull(b.name,c.name) as name from ta a left join tb b on a.id = b.id and a.type = 1 left join tc c on a.id = c.id and a.type = 2 /* id name ----------- ---------- 1 张三 2 李四 3 王二麻子 4 冷箫轻笛(4 行受影响)*/
union all
select * from ta a left join c on a.id=c.id and a.type=2
from a left join b on ....
where a.type=1
union all
select *
from a left join c on ....
where a.type=2
select a.id, isnull(b.c1,c.c1),isnull(b.c2,c.c2),....
from ta a left join tb b on a.id = b.id and a.type = 1
left join tc c on a.id = c.id and a.type = 2
from a
left inner join b
on a.type=1 and 连接条件
union all
select *
from a
left inner join c
on a.type=2 and 连接条件
create table ta
(
id int,
type int
)create table tb
(
id int,
name varchar(10)
)create table tc
(
id int,
name varchar(10)
)insert into ta select 1,1
insert into ta select 2,1
insert into ta select 3,2
insert into ta select 4,2insert into tb select 1,'张三'
insert into tb select 2,'李四'
insert into tc select 3,'王二麻子'
insert into tc select 4,'冷箫轻笛'--查询
select a.id,case a.type when 1 then b.name when 2 then c.name end as name
from ta a left join tb b on a.id = b.id and a.type = 1
left join tc c on a.id = c.id and a.type = 2--结果
/*
id name
----------- ----------
1 张三
2 李四
3 王二麻子
4 冷箫轻笛(4 行受影响)*/--删除环境
drop table ta
drop table tb
drop table tc
--查询
select a.id,isnull(b.name,c.name) as name
from ta a left join tb b on a.id = b.id and a.type = 1
left join tc c on a.id = c.id and a.type = 2
/*
id name
----------- ----------
1 张三
2 李四
3 王二麻子
4 冷箫轻笛(4 行受影响)*/