left join 子查询报错
问问子查询left join报错的问题,
select * from a left join ( select b.id1,c.c1 from b,c where b.id2 = c.id2 ) f on a.id1 = f.id1
语句执行报语法错误,请各位大侠帮忙看看怎么修改,谢谢
select * from a left join ( select b.id1,c.c1 from b,c where b.id2 = c.id2 ) f on a.id1 = f.id1
语句执行报语法错误,请各位大侠帮忙看看怎么修改,谢谢
create table a( id1 integer, a1 integer ); insert into a values(1,1); insert into a values(2,2); insert into a values(3,3); insert into a values(4,4); insert into a values(6,6); insert into a values(7,7); create table b( id1 integer, id2 integer, b1 integer); insert into b values(1,1,1); insert into b values(3,3,3); insert into b values(5,5,5); create table c ( id2 integer, c1 integer); insert into c values(1,1); --执行报错 select * from a left join ( select b.id1,c.c1 from b,c where b.id2 = c.id2 ) f on a.id1 = f.id1 --变通方法,执行成功 select b.id1,c.c1 from b,c where b.id2 = c.id2 into temp temp2 with no log; select * from a left join temp2 f on a.id1 = f.id1; ----方法3,结果不对 select a.*,c.c1 from a,outer b , c where a.id1 = b.id1 and b.id2 = c.id2
附件：
IDS 教程指南.pdf (3.23 MB)
4同行回答
9.40中还真不支持你想使用的这个语法。。估计按 @创鬼 的方法可行。
select * from a left join ( select b.id1,c.c1 from b,c where b.id2 = c.id2 ) f on a.id1 = f.id1收起
浏览1728
浏览1716
回复 2# lansworld
select b.id1,c.c1 from b,c where b.id2 = c.id2 into temp temp2 with no log;
select * from a left join temp2 f on a.id1 = f.id1;
你要的结果：
id1 a1 id1 c1
1 1 1 1
2 2
3 3
4 4
6 6
7 7
实现方法：
select a.*,b.id1,c.c1 from (b inner join c on b.id2=c.id2 )
right outer join a on b.id1=a.id1;
结果：
id1 a1 id1 c1
1 1 1 1
2 2
3 3
4 4
6 6
7 7
你自己再实际测试下，
我是将left join 换成了 right join
附件是9.4的SQL教程，你可以参考下收起
select b.id1,c.c1 from b,c where b.id2 = c.id2 into temp temp2 with no log;
select * from a left join temp2 f on a.id1 = f.id1;
你要的结果：
id1 a1 id1 c1
1 1 1 1
2 2
3 3
4 4
6 6
7 7
实现方法：
select a.*,b.id1,c.c1 from (b inner join c on b.id2=c.id2 )
right outer join a on b.id1=a.id1;
结果：
id1 a1 id1 c1
1 1 1 1
2 2
3 3
4 4
6 6
7 7
你自己再实际测试下，
我是将left join 换成了 right join
附件是9.4的SQL教程，你可以参考下收起
浏览2014