再多套一层。类似这样

mysql 子查询不支持limit - xuzhujack - 博客园 (cnblogs.com)

and produce year in (

      SELECT produce year from( SELECT distinct produce year from benchmarking index where produce year<='2022'order by produce year desc limit 5))

and produce_year in(SELECT distinct produce year from benchmarking_index where produce year<= '2022’order by produce year desc limit 5)

改成  and exists ( select * from (SELECT distinct produce_year from benchmarking_index where produce year<= '2022’order by produce year desc limit 5 ) a where a.produce_year=benchmarking_index.produce_year)

试试，没用MYSQL无法验证

produce_year 

between 选择的年份 - 5 and 选择的年份然后用year （选择日期） 获取年份