三表联合查询,获取某值最大记录
有表   a,b,c

表a有字段
a1,a2,a3,a4

a2字段为日期类型

表b有字段
b1,b2,b3,b4

表c有字段
c1,c2,c3,c4

a1=b1
c1=b2

a表与b表通过a1,b1是1对n关系
c表与b表通过c1,b2是1对n关系

现在要查询返回
a1,a2,a3,a4,b2,b3,b4,c2,c3,c4
并且a2即日期为最大的一条记录,返回记录根据c3排序.

谢谢!

------解决方案--------------------
select a1,a2,a3,a4,b2,b3,b4,c2,c3,c4
from (
select * from a x where
not exists (select 1 from a where x.a1=a1 and x.a2 <a2))y
inner join b on y.a1=b.b1
inner join c on c.c1=b.b2

?
------解决方案--------------------
select a.a1,a.a2,a.a3,a.a4,b.b2,b.b3,b.b4,c.c2,c.c3,c.c4
from a join b on a.a1=b.b1 join c on b.b2=c.c1 join (select b.b1 as d1,max(a.a2) from a join b on a.a1=b.b1 group by b.b1) on b.b1=d1 order by c.c3

没数据，不知可行不。。
------解决方案--------------------

select a.a1,a.a2,a.a3,a.a4,b.b2,b.b3,b.b4,c.c2,c.c3,c.c4
from a
inner join b on a.a1=b.b1
inner join c on b.b2=c.c1
inner join (select a1,max(a2) as a2 from a group by a1) as d on a.a1=d.a1 and a.a2=d.a2
------解决方案--------------------


select a.a1,a.a2,a.a3,a.a4,b.b2,b.b3,b.b4,c.c2,c.c3,c.c4
from a
inner join b on a.a1=b.b1
inner join c on b.b2=c.c1
inner join (select a1,max(a2) as a2 from a group by a1) as d on a.a1=d.a1 and a.a2=d.a2
order by c.c3
------解决方案--------------------
select Y.a1,Y.a2,Y.a3,Y.a4,b.b2,b.b3,b.b4,c.c2,c.c3,c.c4
from b inner join c on b.b2=c.c1
inner join
(select * from a X where not exists(select 1 from a where a3=X.a3 and a2> X.a2))Y
on Y.a1=b.b1
------解决方案--------------------
--方法一
Select a.a1, a.a2, a.a3, a.a4, b.b2, b.b3, b.b4, c.c2, c.c3, c.c4
From a
Inner Join b
On a.a1 = b.b1
Inner Join c
On b.b2 = c.c1
Where Not Exists(Select * From a a1 Inner Join b b1 On a1.a1 = b1. b1 Where b1.b2 = b.b2 and a1.a2 > a.a2)
Order By c.c3


--方法二
Select a.a1, a.a2, a.a3, a.a4, b.b2, b.b3, b.b4, c.c2, c.c3, c.c4
From a
Inner Join b
On a.a1 = b.b1
Inner Join c
On b.b2 = c.c1
Where a2 =
(Select Max(a2) From a a1 Inner Join b b1 on a1.a1 = b1.b1 Where b1.b2 = b.b2)
Order By c.c3


--方法三
Select a.a1, a.a2, a.a3, a.a4, b.b2, b.b3, b.b4, c.c2, c.c3, c.c4
From a
Inner Join b
On a.a1 = b.b1
Inner Join c
On b.b2 = c.c1
Inner Join
(Select b2, Max(a2) As a2 From a Inner Join b on a.a1 = b.b1 Group By b2) d
On a.a2 = d.a2 and b.b2 = d.b2
Order By c.c3