我有一个名为“rates”的表,它有两个字段“date”和“rate”。我喜欢获取每个月的最小和最大速率值及其发生的日期。但我无法做到。
选择日期,
MIN(速率) AS minRate,
MAX(速率) AS maxRate,
MONTH(日期) AS 月份名称,
YEAR(日期) AS 年名
从费率
GROUP BY 年名 ASC、月名 ASC
澄清:我喜欢得到这样的东西:
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
等等
尝试这个查询,数据库名称是 test,您可以使用您的数据库名称或将其删除:
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
)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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)