我有4个表 分别为 tab1,tab2,tab3,tab4tab1如下:id name tab2_id tab3_id tab4_idtab2如下:
id name re tab3如下:
id nametab4如下:
id name我想得到这样一个表
tab_temp
id name tab2_name tab2_re tab3_name tab4_name请问
如何写SQL把这几个表连接起来,得到一个 tab_temp 的表高手们请指教
id name re tab3如下:
id nametab4如下:
id name我想得到这样一个表
tab_temp
id name tab2_name tab2_re tab3_name tab4_name请问
如何写SQL把这几个表连接起来,得到一个 tab_temp 的表高手们请指教
FROM Tab1 T1 LEFT JOIN
Tab2 T2 ON T1.ID=T2.ID LEFT JOIN
Tab3 T3 ON T1.ID=T3.ID LEFT JOIN
Tab4 T4 ON T1.ID=T4.ID
SELECT T1.ID AS ID,T1.NAME AS NAME,T2.NAME AS tab2_name,T2.REMARK AS tab2_re,T3.NAME AS tab3_name,T4NAME AS tab4_name
FROM Tab1 T1 LEFT JOIN
Tab2 T2 ON T1.tab2_id=T2.ID LEFT JOIN
Tab3 T3 ON T1.tab3_id=T3.ID LEFT JOIN
Tab4 T4 ON T1.tab4_id=T4.ID
insert tab1
select 1,'张','2','3','4' union
select 2,'陈','2','3','4'
select * from tab1
create table tab2(id int,name varchar(10),re varchar(200))
insert tab2
select '2','王','姓王的'
create table tab3(id int,name varchar(10))
insert tab3
select '3','刘'
create table tab4(id int,name varchar(10))
insert tab4
select '4','韩'SELECT T1.ID AS ID,T1.NAME AS NAME,T2.NAME AS tab2_name,T2.REMARK AS tab2_re,T3.NAME AS tab3_name,T4.NAME AS tab4_name
FROM Tab1 T1 LEFT JOIN
Tab2 T2 ON T1.tab2_id=T2.ID LEFT JOIN
Tab3 T3 ON T1.tab3_id=T3.ID LEFT JOIN
Tab4 T4 ON T1.tab4_id=T4.IDdrop table tab1,tab2,tab3,tab4
(2 行受影响)
id name tab2_id tab3_id tab4_id
----------- ---------- ----------- ----------- -----------
1 张 2 3 4
2 陈 2 3 4(2 行受影响)(1 行受影响)(1 行受影响)
(1 行受影响)
ID NAME tab2_name tab2_re tab3_name tab4_name
----------- ---------- ---------- -------------------- ---------- ----------
1 张 王 姓王的 刘 韩
2 陈 王 姓王的 刘 韩(2 行受影响)
left join tab2 on tab1.id = b.id
left join tab3 on tab1.id = c.id
left join tab4 on tab1.id = d.id