mysql - 如何选择具有日期频率的逾期行？

Question

+------------------------+--------+
|  Invoice_id | due_date | amount |
+-------------+----------+--------+
|  20         |2020-01-18|  1250  |
+-------------+----------+--------+
| 21          |2020-01-15|  1335  |
+-------------+----------+--------+

获取日期已过的所有记录n days and its multiple serires就像下面...

例如n=5

SELECT * FROM `invoices` 
WHERE `due_date = DATE_ADD(CURDATE() + INTERVAL 5 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 10 days)
OR due_date = DATE_ADD(CURDATE() + INTERVAL 15 days)`

但我想让它对任何 n value 通用

Answer 1

实现此目的的一种方法是生成一个相差 5 天的日期范围，然后将其与您的表格连接起来 -

SELECT *
FROM `invoices` I
JOIN (SELECT a.Date
      FROM (SELECT CURDATE() + INTERVAL (a.a + (10 * b.a) + (100 * c.a) ) * 5 DAY as Date
            FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as b
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as c
           )a
      WHERE a.Date <= (SELECT MAX(due_date) FROM `invoices`)
     ) ON I.due_date = a.Date

我在这里只生成了 1000 行。如果您的表太大，那么您可能会使用 1 个以上的交叉连接生成 10000 行。