急求SQLserver sql语句

[铁克禁卫军]

有如下4张表e_building_src、e_floor、e_floor_flatplan、e_flatplan。
现在做关联查询：
e_building_src 有字段ID、nature_number、land_id.ID为主键
e_floor 有字段floor_id、nature_number、land_id,floor_id为主键，
e_floor_flatplan 有字段ID、floor_id、flatplan_id,ID为主键,floor_id为e_floor表的外键，flatplan_id为e_flatplan表ID的外键
e_flatplan 有字段ID、dwgfile、dwgfilename，ID为主键。
举例
e_building_src e_floor e_floor_flatplan
ID nature_number land_id flood_id nature_number land_id ID floor_id flatplan_id
1 1 1 1 1 1 1 1 01
2 2 1 2 1 1 2 1 02
3 3 1 3 2 1 3 2 03
4 1 2 4 3 1 4 3 04
5 2 2 5 1 2 5 3 05
6 1 3 6 2 2 6 4 06
7 1 3 7 5 07
8 6 08
9 6 09
10 6 10
11 7 11
现在要求是我要先上面三张表关联得到在e_floor_flatplan表同一个floor_id下取最大的flatplan_id值的记录。再通过flatplan_id与第四张表e_flatplan 的ID关联 查询第四张表相关的记录

[铁克禁卫军]

有如下4张表e_building_src、e_floor、e_floor_flatplan、e_flatplan。
现在做关联查询：
e_building_src 有字段ID、nature_number、land_id.ID为主键
e_floor 有字段floor_id、nature_number、land_id,floor_id为主键，
e_floor_flatplan 有字段ID、floor_id、flatplan_id,ID为主键,floor_id为e_floor表的外键，flatplan_id为e_flatplan表ID的外键
e_flatplan 有字段ID、dwgfile、dwgfilename，ID为主键。
举例
e_building_src
ID nature_number land_id
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 1 3

e_floor
flood_id nature_number land_id
1 1 1
2 1 1
3 2 1
4 3 1
5 1 2
6 2 2
7 1 3

e_floor_flatplan
ID floor_id flatplan_id
1 1 01
2 1 02
3 2 03
4 3 04
5 3 05
6 4 06
7 5 07
8 6 08
9 6 09
10 6 10
11 7 11

现在要求是我要先上面三张表关联得到在e_floor_flatplan表同一个floor_id下取最大的flatplan_id值的记录。再通过flatplan_id与第四张表e_flatplan 的ID关联 查询第四张表相关的记录

[Vidor]

有前两张表什么事？简单问题复杂化：

select * from e_floor_flatplan a, e_flatplan b
where a.flatplan_id=b.id
and not exists (select 1 from e_floor_flatplan where floor_id=a.floor_id and flatplan_id>a.flatplan_id)