PHP + MySQL中的递归逻辑 [英] Recursive logic in PHP + MySQL

问题描述

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

I am trying to apply recursive logic.

我在tbl_appointment表中有以下数据(recur_type:1 =每周，2 =每月，0 =不递归):

I have the following data in tbl_appointment table (recur_type: 1 = weekly, 2 = monthly, 0 = not recursive):

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个月)的数据，则

I have the following criteria to fetch data: If I fetch data for july 2014 (7th month) then

1. appointment_id(21)已被提取(1行)，并且…

2. appointment_id(18)是每周递归的，七月份的重复数据被提取(4行):

1. appointment_id(21) is fetched (1 row) and…

2. appointment_id(18) is weekly recursive, repetitions in july are fetched (4 rows):

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.

Note: Date is changed because appointment is recursive for each week, which means I add 7 days to each date. 2014-06-02 + 7 days = 2014-06-09 and so on. Thus for july, date is 2014-07-07.

appointment_id(19)是每月递归的，七月份的重复数据被提取(1行):

appointment_id(19) is monthly recursive, repetitions in july are fetched (1 row):

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

注意:日期已更改，因为约会是每个月递归的，这意味着我要向日期添加一个月.

Note: Date is changed because appointment is recursive for each month, which means I add one month to the date.

最终输出为(总共6行):

Final output is (6 rows in total):

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

如何满足上述要求?

推荐答案

使用查询结合在一起的每种递归类型.

Using a query for each recursion type, unioned together.

每周和每月递归使用几个交叉连接的查询来生成一系列数字，以添加到日期中.这可以处理多达1000个重复约会，但很容易扩展到更多(如果约会要重复20多年以上).

The weekly and monthly recursions use a couple of cross joined queries to generate a range of numbers to add to the date. This copes with up to 1000 repeated appointments but is easily expanded to more (if the appointments want to repeat for more than ~20 years ).

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在这里摆弄:-

SQL fiddle for this here:-

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

这篇关于PHP + MySQL中的递归逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！