mysql - 显示日期之间的数据并循环遍历年份

Question

我正在尝试编写一个查询，该查询将返回任务的开始日期和结束日期在发布日期之间的所有数据，并且如果设置了开始日期但结束日期为0000-00- 00 那么数据将继续出现在结果中，因为它没有结束日期。此外，结果应该显示我们所在的那周，即 NOW()。因此，如果我们比较发布日期的周和当前周，发布日期的周较高，则应该出现结果。

我的数据库结构如下所示:

id  cons_id     day_name        start_date      end_date        status_code
___________________________________________________________________________
1   105         Wednesday       2017-05-17      0000-00-00      0
2   106         Wednesday       2017-05-24      2018-06-20      0
3   107         Thursday        2017-05-25      2030-02-28      0
4   108         Friday          2017-05-19      2019-01-25      0
5   109         Wednesday       2018-01-03      2018-02-14      0

我的查询如下所示:

SELECT i.cons_id AS 'cons_id',
                i.type AS 'type',
                i.day_name AS 'day_name',
                i.day_num AS 'day_num',
                i.start_date AS 'start_date',
                i.end_date AS 'end_date',
                i.created_at AS 'created_at',
                i.updated_at AS 'updated_at'
                FROM recurent_cons AS i
                WHERE i.status_code = '0'
                AND ('2017-05-26' BETWEEN i.start_date AND i.end_date)
                AND i.end_date >= '2017-05-26'
                OR i.end_date = '0000-00-00'

上面的查询即使在本周也显示 id 1 的记录，这是不正确的，而且当我将日期更改为年份 2018 时，它不会显示今年 2017 的记录，记录的结束日期为 2018

所以结果会是这样的:

如果我选择 2017-05-24，我应该看到:

id  cons_id     day_name        start_date      end_date        status_code

<小时/>

1   105         Wednesday       2017-05-17      0000-00-00      0
3   107         Thursday        2017-05-25      2030-02-28      0
5   109         Wednesday       2018-01-03      2018-02-14      0

排除当前日期并显示 future 日期加上无穷大日期 (0000-00-00)

Answer 1

对于 id=1 的条目，最后一个 OR i.end_date = '0000-00-00' 为 true，这就是它显示在查询结果中的原因。

您可能想这样做:

SELECT i.cons_id AS 'cons_id',
       i.type AS 'type',
       i.day_name AS 'day_name',
       i.day_num AS 'day_num',
       i.start_date AS 'start_date',
       i.end_date AS 'end_date',
       i.created_at AS 'created_at',
       i.updated_at AS 'updated_at'
FROM recurent_cons AS i
WHERE i.status_code = '0'
    AND ('2017-05-26' BETWEEN i.start_date AND i.end_date)
    AND (i.end_date >= '2017-05-26' OR i.end_date = '0000-00-00')

如果您想要当前日期、 future 日期或尚未结束的所有条目:

SELECT i.cons_id AS 'cons_id',
       i.type AS 'type',
       i.day_name AS 'day_name',
       i.day_num AS 'day_num',
       i.start_date AS 'start_date',
       i.end_date AS 'end_date',
       i.created_at AS 'created_at',
       i.updated_at AS 'updated_at'
FROM recurent_cons AS i
WHERE i.status_code = '0' 

    AND (i.start_date >='2017-05-26' # Future and current
         OR i.end_date = '0000-00-00') # Didn't finish yet