首页 文章

选择N个月前在给定日期创建的所有记录

提问于
浏览 次
3

给出一个表 orders ：

+-----+---------+-------------------------+
| id  |  price  |         created_at      |
+-----+---------+-------------------------+
|  1  |  16.50  | 2017-02-28 12:52:00.824 |
|  2  |  22.00  | 2017-10-03 15:12:39.107 |
|  3  |  50.00  | 2017-12-03 12:54:42.658 |
|  4  |  12.00  | 2018-01-02 07:21:47.808 |
|  .  |   .     |             .           |
|  .  |   .     |             .           |
|  .  |   .     |             .           |
+-----+---------+-------------------------+

和当前日期：

+---------------------+
| NOW()               |
+---------------------+
| 2018-01-03 10:33:14 |
+---------------------+

我想选择 all records that were created on current day any months ago . 所以对于上面的数据我的查询应该返回：

+-----+---------+-------------------------+
| id  |  price  |         created_at      |
+-----+---------+-------------------------+
|  2  |  22.00  | 2017-10-03 15:12:39.107 |
|  3  |  50.00  | 2017-12-03 12:54:42.658 |
+-----+---------+-------------------------+

但是在一个月的最后一天有一些 edge cases ：

  • 如果它's 31-days month, it'是微不足道的

  • 如果是30个月，则查询应返回在每月30日和31日创建的所有记录

  • 如果是闰年2月，则查询应返回每月29日，30日和31日创建的所有记录

  • 如果是正常年份的2月，则查询应返回在月份的28日，29日，30日和31日创建的所有记录

我试过的是这样的：

SELECT * FROM orders
JOIN (
  SELECT id, PERIOD_DIFF(
    DATE_FORMAT(NOW(), "%Y%m"),
    DATE_FORMAT(created_at, "%Y%m")
  ) AS diff
  FROM orders
) AS periods
ON orders.id = periods.id
WHERE DATEDIFF(created_at + INTERVAL diff MONTH, NOW()) = 0;

但它没有涵盖边缘情况，我相信有一种更聪明的方法（可能没有子查询）来实现预期的结果 .

EDIT: 为了给你更多的背景 - 我需要的是一种循环 . 我有一个 cron job 计划每天午夜运行一次 . 此作业应选择几个月前在此日创建的所有 ids orders ，然后刷新与这些 ids 相关的其他数据 . 重要的是每月刷新一次这个数据 - 这就是为什么几个月的最后一天如此重要 .

例如，给定以下创建日期：

DATES = [
  2015-05-30, 2016-02-29, 2016-10-03,
  2016-12-31, 2017-05-28, 2018-01-03
]

+---------------+------------------------------------+
|     NOW()     |         SHOULD BE INCLUDED         |
+---------------+------------------------------------+
|  2018-01-03   | 2016-10-03, 2018-01-03             |
|  2018-02-28   | 2016-02-29, 2016-12-31, 2017-05-28 |
|  2018-04-30   | 2015-05-30, 2016-12-31             |
|  2018-10-31   | 2016-12-31                         |
+---------------+------------------------------------+
mysql sql

3 回答

  • 0

    我可以建议稍微简化Walerian吗？

    SELECT 
    *

FROM 
    orders

WHERE 
    (
        DAYOFMONTH(created_at) = DAYOFMONTH( NOW() ) --monthdays that match
    )
    OR
    (
        (  DAYOFMONTH(  LAST_DAY( NOW() )  ) = DAYOFMONTH( NOW() )  ) --base date is end of month
        AND
        (  DAYOFMONTH(created_at) > DAYOFMONTH( NOW() )  ) --target monthdays are beyond base monthday
    )

    顺便说一下，我没有MySQL环境，所以我只是相信它们是MySQL中正确的功能 .

    回复于
  • 1

    使用DAYOFMONTH（）函数比较NOW（）和created_at的日期 . 像这样：

    SELECT * FROM ORDERS
WHERE (DAYOFMONTH(NOW() < LAST_DAY(NOW()) -- if not last day of month 
        AND DAYOFMONTH(created_at) = DAYOFMONTH(NOW())
OR (LAST_DAY(NOW()) = DAYOFMONTH(NOW()) -- if last day of month
     AND DAYOFMONTH(NOW()) BETWEEN DAYOFMONTH(created_at) AND LAST_DAY(created_at)) --
    回复于
  • 1

    受suresubs's答案的启发，我弄清楚查询应该是什么样子 .

    SELECT * FROM orders
-- (1)
WHERE (DAYOFMONTH(NOW) < DAYOFMONTH(LAST_DAY(NOW()))
  AND DAYOFMONTH(created_at) = DAYOFMONTH(NOW()))
-- (2)
OR (DAYOFMONTH(LAST_DAY(NOW())) = DAYOFMONTH(NOW())
  AND DAYOFMONTH(created_at) BETWEEN DAYOFMONTH(NOW()) AND DAYOFMONTH(LAST_DAY(created_at)));

    它使用DAYOFMONTH()和LAST_DAY()函数，它分为两种情况：

    • 今天是 not 本月的最后一天 .

    • 今天是本月的最后一天 .

    回复于

相关问题