根据年份的条件 MySQL 查询

Question

我有一个 mysql 表，价格像

id  element_id  daily_price   weekly_price   since          until 
----------------------------------------------------------------------
1       2           400           2800        2017-01-01   2017-05-31
2       2           500           3500        2017-06-01   2017-12-31
3       2           600           4200        2018-01-01   2018-05-31
4       2           700           4900        2018-06-01   2018-12-31

我想做一个查询，获取当年的最低daily_price和weekly_price。如果没有为当年设定价格，那么它应该得到与去年相同的最低价格。

Answer 1

你可以用这个来做

SELECT MIN(daily_price) as minDailyPrice, Max(weekly_price) as maxWeeklyPrice 
FROM yourTable 
WHERE until <= 'current_year_here' 
ORDER BY until DESC 
LIMIT 1

Answer 2

我认为这是最简单的方法：

select min(daily_price), min(weekly_price)
from table 
where element_id = 2
AND year(since) = (select max(year(since))
from table
where element_id = 2);

或者如果你想使用连接而不是子查询：

select min(daily_price), min(weekly_price)
from table a, (select max(year(since)) as year, element_id
from table
where element_id = 2) b
where a.element_id = 2
and a.element_id = b.element_id
and year(since) = b.year;

Answer 3

您可以使用带有年、最小每日和最小每周子查询的连接

select  a.id, a.year, b. min_daily, c.min_weekly 
from  (
select id, max(year(until)) year 
from my_table  
group by id 
) a 
inner join  (
select id, year(until) year,  min(daily_price)  min_daily
from my_table  
group by id ,  year(until)  
) b on a.id = b.id and a.year=b.year 
inner join  (
select  id,  year(until) year, min(weekly_price) min_weekly
from my_table  
group by id ,  year(until) 
) c on a.id = c.id and a.year=c.year