您可以使用相关子查询来完成此操作。以下内容将返回准确日期或最近的前一日期的价格：

SELECT dates.dt, (
    SELECT price
    FROM t
    WHERE date <= dates.dt
    ORDER BY date DESC
    LIMIT 1
) AS price
FROM (
    SELECT '2018-03-31' AS dt UNION ALL
    SELECT '2017-03-31' UNION ALL
    SELECT '2016-12-31' UNION ALL
    SELECT '2015-12-31'
) AS dates

在db上演示

看来你需要一年的最高价格

select max(date), max(price)
from my_table  m 
inner join  (

  select max(date), year(date) my_year
  from my_table 
  group by year(date) 

) t on t.my_year = year(m.date) 
group by year(m.date)

您可以使用mysql last day函数和date\ u格式来实现所需的结果。查看查询：-

select last_day(a11.d_date), a11.price
from test a11
join
(select MAX(d_date) d_date, DATE_FORMAT(d_date, "%M %Y")
from test
group by DATE_FORMAT(d_date, "%M %Y")
) a12
on a11.d_date = a12.d_date

sql小提琴