如果日期不存在,则获取给定日期的最后一行

您可以对相关的子查询执行此操作。以下内容将返回确切日期或最早日期的价格:

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

Demo on db<>fiddle

您可以使用mysql last day函数和date_格式来获得所需的结果。查看查询:

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 Fiddle

似乎你需要一年的最高价格

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)