急求SQLserver sql语句

Question

有如下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关联 查询第四张表相关的记录

Answer 1

有如下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关联 查询第四张表相关的记录

Answer 2

有前两张表什么事？简单问题复杂化：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)