PHP + MySQL 中的递归逻辑

我正在尝试应用递归逻辑。

我有以下数据tbl_appointment table (recur_type：1 = 每周，2 = 每月，0 = 非递归）：

appointment_id    user_id    date        recur_type    .....
18                56         2014-06-02  1
19                56         2014-06-15  2
20                56         2014-06-20  0
21                56         2014-07-20  2

我有以下标准来获取数据：如果我获取数据2014年7月（第7个月）然后

1. appointment_id(21)已获取（1 行）并且...

2. appointment_id(18)是每周递归，获取 7 月份的重复数据（4 行）：

   appointment_id    user_id    date        recur_type    .....
   18                56         2014-07-07  1
   18                56         2014-07-14  1
   18                56         2014-07-21  1
   18                56         2014-07-28  1
   

   注意：日期已更改，因为约会每周都是递归的，这意味着我为每个日期添加 7 天。 2014-06-02 + 7 天 = 2014-06-09 等等。因此，对于 7 月，日期为 2014-07-07。

3. appointment_id(19)是每月递归，获取 7 月份的重复数据（1 行）：

   appointment_id    user_id    date        recur_type    .....
   19                56         2014-07-15  2
   

   注意：日期已更改，因为约会对于每个月都是递归的，这意味着我在日期上添加了一个月。

最终输出为（共6行）：

appointment_id    user_id    date        recur_type    .....
21                56         2014-07-20  2
18                56         2014-07-04  1
18                56         2014-07-11  1
18                56         2014-07-18  1
18                56         2014-07-15  1
19                56         2014-07-15  2

我尝试了以下代码：

SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    0 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND YEAR(ta.date) = '2014'
      AND MONTH(ta.date) = '06'
      AND ta.user_id = 56
UNION ALL SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    1 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND recur_type = '2'
      AND ta.user_id = 56
UNION ALL SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    2 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND recur_type = '1'
      AND ta.user_id = 56
ORDER BY date DESC, time

如何满足以上要求呢？

对每种递归类型使用联合查询。

每周和每月递归使用几个交叉连接查询来生成一系列数字以添加到日期中。这可以处理最多 1000 次重复约会，但很容易扩展到更多（如果约会想要重复超过约 20 年）。

SELECT a.appoinemnt_id, a.user_id, a.recur_type, a.date AS appoint_date
FROM tbl_appointment a
WHERE a.recur_type = 0
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'
UNION
SELECT a.appoinemnt_id, a.user_id, a.recur_type, DATE_ADD(a.date, INTERVAL units.i + tens.i * 10 WEEK) AS appoint_date
FROM tbl_appointment a
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE a.recur_type = 1
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'
UNION
SELECT a.appoinemnt_id, a.user_id, a.recur_type, DATE_ADD(a.date, INTERVAL units.i + tens.i * 10 MONTH) AS appoint_date
FROM tbl_appointment a
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE a.recur_type = 2
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'

SQL 在这里进行操作：-

http://www.sqlfiddle.com/#!2/6346a2/2 http://www.sqlfiddle.com/#!2/6346a2/2