如何通过子查询改进/简化查询？

Question

我想知道人的最大形成水平，并且一​​个人可以有许多编队条目。

表t_formation

idFormation | fkPerson | fkLevel  | place 
------------------------------------------------ 
    1  |  1  |  2  | Oxford 
    2  |  2  |  1  | PlaySchool 
    3  |  1  |  3  | Trinity High 
    4  |  1  |  1  | My School 
    5  |  2  |  3  | My High 

表a_level

idLevel | orderLevel | formation 
------------------------------------- 
    1  | 1  | School 
    2  | 3  | University 
    3  | 2  | High school 

我需要得到的是以下查询或所需的查询结果 （最大订单水平与地层每个人和他们研究的地方，最大编队）

fkPerson | maxOrderLevel | formation | place 
---------------------------------------------------- 
     1 |  2  | Univertity | Oxford 
     2 |  3  | High school | My High 

为了做到这一点，我做了一个查询与2个subquerys但它是不可能创造一个高效的视图。

查询SQL没有的地方，每个人的这种获得最大的形成

select fkPerson, a_level.orderLevel, a_level.formation 
from (
    select fkPerson, max(a_level.orderlevel) as ordermax 
    from t_formation left join a_level on t_formation.fkLevel = a_level.idLevel 
    group by fkPerson 
) as form left join a_level on form.ordermax = a_level.orderlevel 

Answer 1

的部分是你可以为每个fkPerson多个orderLevel值，所以你将不得不检索max(orderLevel)每个fkPerson然后执行另一个加入得到与每个人相关的place/formation。

我看到了一些可以得到结果的方法。

您可以使用子查询来获取max(orderLevel)，然后加入你的表，返回所需的列：

select t1.fkPerson, 
    at.MaxOrder, 
    t1.Place, 
    a.Formation 
from t_formation t1 
inner join a_level a 
    on t1.fkLevel = a.idLevel 
inner join 
(
    select t.fkPerson, max(a.orderLevel) MaxOrder 
    from t_formation t 
    left join a_level a 
    on t.fkLevel = a.idLevel 
    group by t.fkPerson 
) at 
    on t1.fkPerson = at.fkPerson 
    and at.maxorder = a.orderLevel; 

见SQL Fiddle with Demo。

，你可以得到的结果的另一种方法是使用一个查询在WHERE子句来过滤不具有该行max(orderLevel)：

select t.fkperson, 
    a.orderlevel, 
    t.place, 
    a.formation 
from t_formation t 
left join a_level a 
    on t.fkLevel = a.idLevel 
where a.orderlevel = (select max(orderlevel) 
         from a_level a 
         inner join t_formation t1 
         on a.idlevel = t1.fklevel 
         where t.fkperson = t1.fkperson 
         group by t1.fkperson); 

见SQL Fiddle with Demo。

请注意，您的示例数据似乎无法提供您请求的结果。最大orderLevel对于fkPerson = 2是3而不是2，所以形成将是University而不是High School。

Answer 2

为什么不使用

SELECT fkPerson, fkLevel,orderLevel, formation, place 
FROM t_formation as a 
LEFT OUTER JOIN a_level as b ON a.fkLevel = b.idLevel 
GROUP BY fkPerson HAVING orderLevel = max(orderLevel) 
; 

？问题的