特定范围的动态最小值（mysql） [英] Dynamic minimum value for specfic range (mysql)

问题描述

我正在寻找一个解决方案SELECT（或以其他方式导出）列C的值（最后3天的最低价格，而不是整列）。

I am looking for a solution to SELECT (or otherwise derive) the values for Column C (minimum price for last 3 days only, not for the whole column).

----------------------------------------
Date          | Unit_ | Low_3_days     | 
              | price |                | 
----------------------------------------
2015-01-01    | 15    | should be: 15  | 
2015-01-02    | 17    | should be: 15  | 
2015-01-03    | 21    | should be: 15  | 
2015-01-04    | 18    | should be: 17  | 
2015-01-05    | 12    | should be: 12  | 
2015-01-06    | 14    | should be: 12  |
2015-01-07    | 16    | should be: 12  | 
----------------------------------------

我的想法围绕以下内容，但产生错误：

My thought revolves around the following, but yielding an error:

select S.Date,Unit_price, 
      (SELECT min(LOW_3_days) 
         FROM table 
        where S.DATE BETWEEN S.DATE-1 
                         and S.DATE-3) 
           AS min_price_3_days
  FROM table AS S

什么是正确的查询，使其工作？数据库使用MySQL。

What is the correct query to get this to work? Database used MySQL.

推荐答案

你很亲密。在使用相关子查询时，始终使用表格别名来清楚列的位置：

You are pretty close. When working with correlated subqueries, always use table aliases to be absolutely clear about where the columns are coming from:

select S.Date, Unit_price, 
       (SELECT min(s2.Unit_Price) 
        FROM table s2
        WHERE s2.DATE BETWEEN s.DATE - interval 3 day and
                              s.DATE - interval 1 day
       ) as min_price_3_days
FROM table S;

这篇关于特定范围的动态最小值（mysql）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！