假设有一下3个表
表A 有code(key), no, name 等字段
表B 有code(key), date, other 等字段
表C 有no(key), date(key), age 等字段现在想查询code='01'的表A和表B中的字段。
同时用no和date同表C外连接查询age字段。想要的下面这样的结果:
code no date age
01 1 20071001 12
01 2 20071002 null
01 3 20071003 null
01 4 20071004 18
....数据是假设的。我想像下面这么写,但执行了以后发现不是想要的结果,在不用子查询的情况下,有什么好方法吗?
请大家指点一下,给个例子,一旦解决马上给分。谢谢!
C RIGHT OUTER JOIN A ON C.no = A.no
RIGHT OUTER JOIN B ON C.date = B.date
表A 有code(key), no, name 等字段
表B 有code(key), date, other 等字段
表C 有no(key), date(key), age 等字段现在想查询code='01'的表A和表B中的字段。
同时用no和date同表C外连接查询age字段。想要的下面这样的结果:
code no date age
01 1 20071001 12
01 2 20071002 null
01 3 20071003 null
01 4 20071004 18
....数据是假设的。我想像下面这么写,但执行了以后发现不是想要的结果,在不用子查询的情况下,有什么好方法吗?
请大家指点一下,给个例子,一旦解决马上给分。谢谢!
C RIGHT OUTER JOIN A ON C.no = A.no
RIGHT OUTER JOIN B ON C.date = B.date
from a
inner join b
on a.code=b.code
inner join c
on c.no=a.no and c.date=b.date
这样?
select a.code,a.no,b.date,c.age
from a, b,c
where a.code=b.code and c.no=a.no and c.date=b.date
select a.code,a.no,b.date,c.age
from a join b on a.code=b.code
left join c on c.no=a.no and c.date=b.date
select c.* from c
left join a on c.no = a.no where a.code = '01'select c.* from c
left join b on c.date = b.date where b.code = '01'select c.* from
left join a on c.no = a.no
left join b on c.date = b.date
where a.code = '01' and b.code = '01'
from a
inner join b
on a.code=b.code
inner join c
on c.no=a.no and c.date=b.date
where a.code='01'