php - 更改 mysql 查询以仅在指定日期显示任务

Question

我正在做一个交通规划器，为了按选定的日期显示任务，我正在使用以下 mysql 查询:

    SELECT *
    FROM planner
    WHERE '$date' >= date
    AND '$date' <= valid_till
    AND (frequency = 'daily'
    OR (frequency = 'once' AND date('$date') = date)
    OR (frequency = 'weekly' AND 0 = (abs(datediff('$date', date)) % 7))
    OR (frequency = 'two-weekly' AND 0 = (abs(datediff('$date', date)) % 14))
    OR (frequency = 'monthly'
    AND ceil(dayofmonth('$date')/7) = ceil(dayofmonth(date)/7)
    AND dayofweek('$date') = dayofweek(date)))
    AND dayofweek('$date') <> '1'
    AND dayofweek('$date') <> '7'

现在我有一些任务只需要在星期一、星期三和星期四显示，例如。我以为我为我的计划表创建了一个新列:only_on_days (varchar)，我将其填充为:'2, 4, 5' 其中 2 代表星期一，4 代表星期三，5 代表星期四。

有人可以帮助我更改查询以添加此功能吗？或者也许有更好的想法？

编辑

我创建了一个新表:planner_days，它看起来像(示例):

id --- planner_id --- day
1          12          2
2          12          3
3          12          4
4          12          5
5          12          6
6          13          3
7          13          5

我试过像@eugen-rieck 所说的那样进行连接查询。

SELECT planner.*, planner_days.*
FROM planner
LEFT JOIN planner_days
ON planner.planner_id = planner_days.planner_id
WHERE '$date' >= date
AND '$date' <= valid_till
AND (frequency = 'daily' AND dayofweek('$date') = planner_days.day
OR (frequency = 'once' AND date('$date') = date)
OR (frequency = 'weekly' AND 0 = (abs(datediff('$date', date)) % 7))
OR (frequency = 'two-weekly' AND 0 = (abs(datediff('$date', date)) % 14))
OR (frequency = 'monthly'
AND ceil(dayofmonth('$date')/7) = ceil(dayofmonth(date)/7)
AND dayofweek('$date') = dayofweek(date)))
AND dayofweek('$date') <> '1'
AND dayofweek('$date') <> '7'

这行得通，但现在每天必须显示的任务会显示多次..有人对此有解决方案吗？

Answer 1

永远不要在一个字段中存储多个信息，如果您想分别访问它们。将 CSV 列表放入字段是灾难的秘诀。

规范的方法是与 taskid 联合。和展示它们的日子，然后解决你的AND dayofweek('$date') <> '1' AND dayofweek('$date') <> '7'进入加入