select a.m1,a.a1,a.b1,b.t1,c.t2,b.n1,c.n2 from table1 a left join table2 b on a.m1=b.m2 left join table3 c on a.m1=c.m2
select a.m1,a1,b1,t1,t2,n1,n2 from table1 a left join table2 b on a.m1=b.m2 left join table3 c on a.m1=c.m2
如果我table3要求是一个试图 怎么写拉,
create view test as select a.m1,a1,b1,t1,t2,n1,n2 from table1 a left join table2 b on a.m1=b.m2 left join table3 c on a.m1=c.m2
--加上创建视图语句就行了.create view vtable3 as select a.m1,a.a1,a.b1,b.t1,c.t2,b.n1,c.n2 from table1 a left join table2 b on a.m1=b.m2 left join table3 c on a.m1=c.m2
select a.*,b.t1,c.t1 as t2,b.n1,c.n1 as n2 from table1 a left join (select * from table2 where t1='t1') b on a.m1=b.m1 left join (select * from table2 where t1='t2') c on a.m1=c.m1
原来是这样,那就这样写create view table3 select a.*,b.t1,c.t1 as t2,b.n1,c.n1 as n2 from table1 a inner join ( select * from table2 where t1='t1' ) b on a.m1=b.m1 inner join ( select * from table2 where t1='t2' ) c on a.m1=c.m1
from table1 a left join table2 b on a.m1=b.m2
left join table3 c on a.m1=c.m2
from table1 a
left join table2 b on a.m1=b.m2
left join table3 c on a.m1=c.m2
as
select a.m1,a1,b1,t1,t2,n1,n2
from table1 a
left join table2 b on a.m1=b.m2
left join table3 c on a.m1=c.m2
as
select a.m1,a.a1,a.b1,b.t1,c.t2,b.n1,c.n2
from table1 a left join table2 b on a.m1=b.m2
left join table3 c on a.m1=c.m2
我的意识是把前两个表变成变成第三个表那种格式的视图,不好意识,我可能
没说清楚
你这是什么意思??
副表是两个表table2(m1,t1,n1),table3(m1,t2,n2) ?
还是一个表table2,其中有两条记录(m1,t1,n1),(m1,t2,n2)?
如果是前者,上面的回答都是正确的。
还是一个表table2,其中有两条记录(m1,t1,n1),(m1,t2,n2)
select a.*,b.t1,c.t1 as t2,b.n1,c.n1 as n2
from table1 a inner join (
select * from table2 where t1='t1'
) b on a.m1=b.m1 inner join (
select * from table2 where t1='t2'
) c on a.m1=c.m1
结果是:m1,a1,b1,t1,t2,n1,n2
如果表table2有三条记录(m1,t1,n1),(m1,t2,n2),(m1,t3,n3)
结果是:m1,a1,b1,t1,t2,t3,n1,n2,n3