我有一个名为"rates"的表,它有两个字段"date"one_answers"rate"。我喜欢获取每个月的最低和最高利率值及其发生日期。但我无法应付。
选择日期,
MIN(速率)AS minRate,
MAX(速率)AS maxRate,
MONTH(日期)AS monthName,
YEAR(日期)AS yearName
FROM费率
GROUP BY yearName ASC,monthName 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)