首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >子查询的MySQL - max( )行值

子查询的MySQL - max( )行值
EN

Stack Overflow用户
提问于 2014-01-10 23:58:48
回答 3查看 838关注 0票数 0

我有一张表,每15分钟记录一次降雨量,所以一天的总降雨量是95次记录中的“降雨”之和。下面的查询正确地返回当天的日期和降雨量列表。

代码语言:javascript
运行
复制
select thedate as tdate,sum(rain) as dailyrain
from  weatherdata
group by year(thedate), month(thedate), day(thedate)

(日期存储为日期时间,雨存储为整数)

我现在想把它作为一个子查询,并返回最大降雨量的日期。

下面的查询正确返回最大降雨量,但不返回与该降雨量相关联的正确日期。

代码语言:javascript
运行
复制
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

我想我可能需要加入。我知道这里已经回答了许多类似的问题,但我不能完全正确地使用语法。任何帮助都将不胜感激。

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2014-01-11 00:08:04

代码语言:javascript
运行
复制
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 ;

或者:

代码语言:javascript
运行
复制
SELECT DATE(thedate) as tdate, sum(rain) as dailyrain
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY dailyrain DESC, tdate DESC
LIMIT 1 ;
票数 2
EN

Stack Overflow用户

发布于 2014-01-11 00:47:05

让所有的日期都有最大的降雨量:

代码语言:javascript
运行
复制
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
         )
票数 1
EN

Stack Overflow用户

发布于 2014-01-11 00:14:58

请尝尝这个

代码语言:javascript
运行
复制
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

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/21056685

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档