时间戳

首页 > 解决方案 > 查询以查找最近的较小日期

mysql - 查询以查找最近的较小日期

问题描述

我有一张桌子，上面有午餐生效日期及其价格。

我需要effective date (created_on)为每date列显示最接近的较小的汇率。

lunch_rate桌子：

created_on |   rate
-----------+-------
2018-06-01 |   30
2018-06-04 |   60

这是我试图做的：

    SELECT userId, 
    SUM(CASE WHEN date= '2018-06-01' AND lunchStatus = 1 THEN (SELECT MAX(rate) FROM lunch_rate WHERE DATE(created_on) <= date LIMIT 1) ELSE 0 END) '2018-06-01',
    SUM(CASE WHEN date= '2018-06-02' AND lunchStatus = 1 THEN (SELECT MAX(rate) FROM lunch_rate WHERE DATE(created_on) <= date LIMIT 1) ELSE 0 END) '2018-06-02',
    SUM(CASE WHEN date= '2018-06-03' AND lunchStatus = 1 THEN (SELECT MAX(rate) FROM lunch_rate WHERE DATE(created_on) <= date LIMIT 1) ELSE 0 END) '2018-06-03',
    SUM(CASE WHEN date= '2018-06-04' AND lunchStatus = 1 THEN (SELECT MAX(rate) FROM lunch_rate WHERE DATE(created_on) <= date LIMIT 1) ELSE 0 END) '2018-06-04'
    FROM
    (        
        SELECT userId, lunchStatus, DATE(issuedDateTime) as date 
        FROM `lunch_status` 
        WHERE DATE(issuedDateTime) BETWEEN '2018-06-01' AND '2018-06-04'        
    ) as a
    GROUP BY userId;

但是这个查询只给出了所有的最大利率，没有考虑最近的生效日期。

结果如下：

userId    |   2018-06-01   |   2018-06-02  |  2018-06-03  |  2018-06-04
------------------------------------------------------------------------
131       |   60           |   60          |   0          |  60   
132       |   60           |   60          |  60          |   0
133       |   0            |    0          |   0          |  60
134       |   0            |    0          |   0          |  60

预期结果：

userId    |   2018-06-01   |   2018-06-02  |  2018-06-03  |  2018-06-04
------------------------------------------------------------------------
131       |   30           |   30          |   0          |  60   
132       |   30           |   30          |  30          |   0
133       |   0            |    0          |   0          |  60
134       |   0            |    0          |   0          |  60

SUM(CASE WHEN ... THEN (SELECT MAX(rate) FROM lunch_rate WHERE DATE(created_on) <= date LIMIT 1) ELSE 0 END) ....',

如何选择在该日期有效的午餐价格？

标签： mysqlsqlcaseaggregate-functions

解决方案

如果我理解正确，您希望在子查询中进行计算：

SELECT userId, 
       SUM(CASE WHEN date = '2018-06-01' AND lunchStatus = 1
                THEN rate ELSE 0
           END) as `2018-06-01`,
       SUM(CASE WHEN date = '2018-06-02' AND lunchStatus = 1
                THEN rate ELSE 0
           END) as `2018-06-02`,
       SUM(CASE WHEN date = '2018-06-03' AND lunchStatus = 1
                THEN rate ELSE 0
           END) as `2018-06-03`,
       SUM(CASE WHEN date = '2018-06-04' AND lunchStatus = 1
                THEN rate ELSE 0
           END) as `2018-06-04`
FROM (SELECT ls.*, DATE(ls.issuedDateTime) as date
             (SELECT lr.rate
              FROM lunch_rate lr
              WHERE DATE(lr.created_on) <= DATE(ls.issuedDateTime)
              ORDER BY lr.created_on DESC
              LIMIT 1
             ) as rate
      FROM lunch_status ls
      WHERE DATE(issuedDateTime) BETWEEN '2018-06-01' AND '2018-06-04'        
     ) lr
GROUP BY lr.userId;

注意其他变化：

  • 的子查询lunch_rate不使用MAX(). 相反，它使用ORDER BY.
  • 列别名被反引号包围，而不是单引号。我不赞成这些名字（因为它们需要被转义）。但是，如果您想要它们，请使用正确的转义字符。
  • 这些表被赋予了合理的别名并且列名是合格的。

推荐阅读