22,207
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb_lei1') is not null
drop table tb_lei1
create table tb_lei1
(
zdbh int,
mc1 varchar(4)
)
if object_id('tb_lei2') is not null
drop table tb_lei2
create table tb_lei2
(
zdbh int,
mc2 varchar(4)
)
if object_id('tb_xm') is not null
drop table tb_xm
create table tb_xm
(
zdbh int,
lei1bh int,
lei2bh int
)
insert tb_lei1
select 1,'AAAA' union all
select 2,'BBBB'
insert tb_lei2
select 1,'CCCC' union all
select 2,'DDDD'
insert tb_xm
select 1,1,null union all
select 1,2,null union all
select 2,null,1 union all
select 3,1,null union all
select 3,null,1 union all
select 3,null,2
--子查询方式
select zdbh,(select mc1 from tb_lei1 where zdbh = tx.lei1bh) lei1mc,(select mc2 from tb_lei2 where zdbh = tx.lei2bh) lei2mc from tb_xm tx
--表连接方式
select tx.zdbh,tl1.mc1,tl2.mc2
from tb_xm tx
left join
tb_lei1 tl1 on tl1.zdbh=tx.lei1bh
left join
tb_lei2 tl2 on tl2.zdbh=tx.lei1bh
/**
zdbh lei1mc lei2mc
----------- ------ ------
1 AAAA NULL
1 BBBB NULL
2 NULL CCCC
3 AAAA NULL
3 NULL CCCC
3 NULL DDDD
(所影响的行数为 6 行)
zdbh mc1 mc2
----------- ---- ----
1 AAAA CCCC
1 BBBB DDDD
2 NULL NULL
3 AAAA CCCC
3 NULL NULL
3 NULL NULL
(所影响的行数为 6 行)
**/
select c.zdbh,a.mc1,b.mc2
from tb_xm c left join tb_lei1 a on c.zdbh=a.zdbh
left join tb_lei2 b on c.zdbh=b.zdbh
SELECT
A.zdbh
B.mc1 AS lei1mc,
C.mc2 AS lei2mc
FROM tb_xm AS A
LEFT JOIN tb_lei1 AS B
ON A.lei1bh = B.zdbh
LEFT JOIN tb_lei2 AS C
ON A.lei2bh = C.zdbh