考虑到我有下表
WindDirection | WindSpeed | Date
W | 12 | 17/01/14
NNW | 20 | 20/02/14
SE | 15 | 30/04/14
ENE | 25 | 21/06/15
NNE | 23 | 10/12/15
WSW | 8 | 07/01/15
S | 19 | 05/06/15
我怎样才能获得每年的最大值？我想实现这一目标
Year | WindSpeed | WindDirection
2014 | 20 | NNW
2015 | 25 | ENE
这是我的查询，它不起作用，只是让您有一个想法
SELECT CONCAT("20", SUBSTRING(`Date`, -2)) AS `Year`, `WindSpeed`, `WindDirection`
FROM `weather`
WHERE `WindSpeed` = (SELECT MAX(`WindSpeed`)
FROM `weather`)
GROUP BY `Year` DESC
提前致谢！
您可以使用以下查询
SELECT B.Year ,
WindSpeed ,
A.WindDirection
FROM weather A
INNER JOIN ( SELECT YEAR ,
MAX(WindSpeed) AS WindSpeed
FROM ( SELECT YEAR(Date) AS YEAR ,
WindSpeed ,
WindDirection
FROM weather
) A
GROUP BY YEAR
) b ON A.WindSpeed = b.WindSpeed
AND B.YEAR = YEAR(A.Date)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句