查询最近的日期

t3irkdon 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(234)

我有一张有午餐生效日期和价格的table。
我需要从最近的较小值显示速率 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) ....',

如何选择当天有效的午餐价格？

sql mysql aggregate-functions case

来源：https://stackoverflow.com/questions/50926333/query-to-find-closest-lesser-date

2条答案

按热度按时间

q3aa05251#

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

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 .
列别名被反引号包围，而不是单引号。我不赞成这些名字（因为它们需要逃走）。但是如果你想要的话，使用合适的转义字符。
表被赋予合理的别名，列名被限定。

赞(0）回复(0）举报 2021-06-20

bd1hkmkf2#

你可以这样做：

SELECT lr1.rate
FROM   lunch_rate lr1
WHERE  lr1.created_on <= my_date
  AND  NOT EXISTS (SELECT *
                   FROM   lunch_rate lr2
                   WHERE  lr2.created_on > lr1.created_on
                     AND  lr2.created_on <= my_date);

赞(0）回复(0）举报 2021-06-20

我来回答

查询最近的日期

2条答案

相关问题

热门标签

最新问答