Mysql最小值和最大值和相应的“日期”每月 [英] Mysql min and max values and corresponding "date" for each month

问题描述

我有一个名为rate的表，它有两个字段date和rate。我想要获得每个月的MIN和MAX价格值及其发生日期。但是我无法管理。

选择日期，

MIN（rate）AS minRate，

MAX（rate）AS maxRate，

MONTH（date）AS monthName，

YEAR（date）AS yearName

FROM rates

GROUP BY yearName ASC，monthName ASC

澄清：我喜欢这样做： / p>

 月份MIN mindate MAX maxdate 
 Jan 1.234 2012-01-13 1.534 2012-01-24 
 Feb 1.165 2012-02-28 1.373 2012-02-11 
  

等等

解决方案

尝试这个查询数据库名称测试是你可以使用你的或删除它

SELECT
MIN（rate）AS minRate，

（从test.rates选择日期，其中rate = min（co.rate）和month（date）= month co.date）和year（date）= year（co.date）limit 1）as min_date，

MAX（rate）AS maxR ate，_
（从test.rates选择日期，其中rate = max（co.rate）和month（date）= month（co.date）和year（date）= year（co.date）限制1）as max_date

FROM test.rates co
GROUP BY年份（日期），月份（日期）

I have a table named "rates" and it has two fields "date" and "rate". I like to get MIN and MAX rate values and their dates on which they occurred for each month. But I could not manage.

SELECT date,
MIN(rate) AS minRate,
MAX(rate) AS maxRate,
MONTH(date) AS monthName,
YEAR(date) AS yearName
FROM rates
GROUP BY yearName ASC, monthName ASC

Clarification: I like to get something like this:

 Months  MIN    mindate     MAX      maxdate  
 Jan     1.234  2012-01-13   1.534  2012-01-24  
 Feb     1.165  2012-02-28   1.373  2012-02-11  

and so on

解决方案

try this query database name test is thier u can use yours or remove it

SELECT MIN(rate) AS minRate,
(select date from test.rates where rate = min(co.rate) and month(date) = month(co.date) and year(date) = year(co.date) limit 1 )as min_date,
MAX(rate) AS maxRate,
(select date from test.rates where rate = max(co.rate) and month(date) = month(co.date) and year(date) = year(co.date) limit 1)as max_date
FROM test.rates co GROUP BY year(date) , month(date)

这篇关于Mysql最小值和最大值和相应的“日期”每月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！