问如何选择身高和体重增长的人？

EN

select p.ID, p.Name
from (
    /* Tells you if the person did not gain weight since the previous year */
    select w.FK_Person, w.Year, case when w1.Weight > w.Weight or w1.Weight is null then 'gain' else 'not gain' end gain
    from Weight w
    left outer join Weight w1 on w.FK_Person = w1.FK_Person and w1.Year = w.year + 1    
) w
join (
    /* Tells you if the person did not gain height since the previous year */
    select w.FK_Person, w.Year, case when w1.Height > w.Height or w1.Height is null then 'gain' else 'not gain' end gain
    from Height w
    left outer join Height w1 on w.FK_Person = w1.FK_Person and w1.Year = w.year + 1    
) h on w.FK_Person = h.FK_Person and w.Year = h.Year
join Person p on p.ID = w.FK_Person
group by p.ID, p.Name
/* Filter out rows where the person had at least one year of not gaining */
having sum(case when w.gain = 'not gain' or h.gain = 'not gain' then 1 else 0 end) = 0

    SELECT P.NAME,W.YEAR,W.WEIGHT,H.HEIGHT FROM PERSON P,
    WEIGHT W,
    HEIGHT H,
    (SELECT MIN(WEIGHT) AS WEIGHT1 FROM WEIGHT WHERE YEAR = 2001) W1,
    (SELECT MIN(HEIGHT) AS HEIGHT1 FROM HEIGHT WHERE YEAR = 2001) H1
    WHERE 
    P.ID = W.FK_PERSON AND
    P.ID = H.FK_PERSON AND
    W.WEIGHT >= W1.WEIGHT1 AND
    H.HEIGHT >= H1.HEIGHT1 

SELECT P.NAME,W.YEAR,W.WEIGHT,H.HEIGHT FROM PERSON P
INNER JOIN WEIGHT W
ON (P.ID = W.FK_PERSON)
WEIGHT W
INNER JOIN HEIGHT H
ON (P.ID = H.FK_PERSON)
HEIGHT H
INNER JOIN
(SELECT MIN(WEIGHT) AS WEIGHT1 FROM WEIGHT WHERE YEAR = 2001) W1
ON (P.ID = W1.FK_PERSON)
INNER JOIN
(SELECT MIN(HEIGHT) AS HEIGHT1 FROM HEIGHT WHERE YEAR = 2001) H1
ON (P.ID = H.FK_PERSON)
WHERE 
W.WEIGHT >= W1.WEIGHT1 AND
H.HEIGHT >= H1.HEIGHT1

select h1.fk_person, h1.height as oldHeight, h1.year as FirstYear, h2.height as NewHeight, h2.year as LastYear, ((h2.height-h1.height)/(h2.year-h1.year))/h1.height as pc_growth_py
from height h1
inner join height h2
  on h1.fk_person = h2.fk_person
where h1.year = (select max(year) from height x1 where x1.person = h1.person)
and h2.year = (select max(year) from height x2 where x2.person = h2.person)

select Name
from Person p1
inner join <Height query> a1
on a1.fk_person = p1.id
inner join <weight query> a2
on a2.fk_person = p1.id
order by a1.pc_growth_py desc, a2.pc_growth_py desc
limit 1