空查询返回

Question

任何帮助，非常感谢。

我有一个表，医院：

Nurse + Year + No.Patients
A001 |2000 | 23
A001 |2001 | 30
A001 |2002 | 35

B001 |2000 | 12
B001 |2001 | 15
B001 |2002 | 45

C001 |2000 | 50
C002 |2001 | 59
C003 |2002 | 69
etc

What I am trying to do is work out which nurse had the greatest increase of patients for the years 2000 - 2002.

Clearly B001 did as her patients increased from 12 to 45 and increase of 33 and what I am trying to produce is the result B001 | 33。

这是我到目前为止有：

select a.nurse,a.nopats from hospital as a 
    join 
    (select nurse,max(nopats)-min(nopats) as growth 
     from hospital where year between 2000 and 2002 group by nurse) as s1 
    on a.nurse = s1.nurse and a.nopats = s1.growth 
    where year between 2000 and 2002; 

，但我得到的返回是一个空集。

我想我需要加入后的整体max（nopats）。

在这里的任何帮助将是伟大的。

谢谢！

Answer 1

试试这个：

SELECT nurse, (max(nopats) - min(nopats)) AS growth 
    FROM hospital 
WHERE year BETWEEN 2000 AND 2002 
GROUP BY nurse 
ORDER BY growth DESC 
LIMIT 1; 

结果：B001 | 33由于限制1;如果您想获得更多结果，请将其丢弃。

Answer 2

SELECT nurse, MAX(nopats) - MIN(nopats) AS Growth 
    FROM hospital 
WHERE year BETWEEN 2000 AND 2002 
GROUP BY nurse 
ORDER BY Growth 

这应该做到这一点。让我知道，如果那就是你需要的。