我有一张表,每15分钟记录一次降雨量,所以一天的总降雨量是95次记录中的“降雨”之和。下面的查询正确地返回当天的日期和降雨量列表。
select thedate as tdate,sum(rain) as dailyrain
from weatherdata
group by year(thedate), month(thedate), day(thedate)
(日期存储为日期时间,雨存储为整数)
我现在想把它作为一个子查询,并返回最大降雨量的日期。
下面的查询正确返回最大降雨量,但不返回与该降雨量相关联的正确日期。
select maxdate,max(dailyrain) from (select thedate as maxdate,sum(rain) as dailyrain
from weatherdata
group by year(thedate), month(thedate), day(thedate)) as maxrain
我想我可能需要加入。我知道这里已经回答了许多类似的问题,但我不能完全正确地使用语法。任何帮助都将不胜感激。
发布于 2014-01-11 00:08:04
SELECT DATE(thedate) as tdate, sum(rain) as dailyrain
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY sum(rain) DESC, DATE(thedate) DESC
LIMIT 1 ;
或者:
SELECT DATE(thedate) as tdate, sum(rain) as dailyrain
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY dailyrain DESC, tdate DESC
LIMIT 1 ;
发布于 2014-01-11 00:47:05
让所有的日期都有最大的降雨量:
SELECT DATE(thedate), SUM(rain)
FROM weatherdata
GROUP BY DATE(thedate)
HAVING SUM(rain) = (
SELECT SUM(rain)
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY SUM(rain) DESC
LIMIT 1
)
发布于 2014-01-11 00:14:58
请尝尝这个
select tdate,dailyrain from
(select date(thedate) as tdate,sum(rain) as dailyrain
from weatherdata
group by date(thedate))r
WHERE dailyrain =
(select max(dailyrain) as maxrain from
(select date(thedate) as tdate,sum(rain) as dailyrain
from weatherdata
group by date(thedate)
)m
)
sqlFiddle
https://stackoverflow.com/questions/21056685
复制相似问题