問題描述

我試圖找到將日期范圍合并到一個數(shù)據(jù)庫記錄(數(shù)組元素)的最佳方法.

I am trying to find the best way on how to merge date ranges into one database record (array element).

這是我擁有的數(shù)據(jù):

  Array
(
    [0] => Array
        (
            [id] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [id] => 18297
            [start_date] => 2011-06-01
            [end_date] => 2011-06-30
        )

    [2] => Array
        (
            [id] => 17113
            [start_date] => 2011-03-31
            [end_date] => 2011-05-31
        )

    [3] => Array
        (
            [id] => 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-03-31
        )
)

在我們組合它們之后，數(shù)組(或數(shù)據(jù)庫)應(yīng)該是這樣的:

And after we combine them, array (or database) should look like this:

Array
(
    [0] => Array
        (
            [merged_ids] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [merged_ids] => 18297, 17113, 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-06-30
        )
)

是否有任何算法可以遍歷所有元素/范圍并將它們組合起來?哪種方式更好/更容易 - 通過數(shù)據(jù)庫 (MYSQL) 或編碼 (PHP)?

Is there any algorithm to go through all elements/ranges and combine them? Which way is better/easier to do - through database (MYSQL) or coding (PHP)?

非常感謝任何建議.

謝謝！

更新:抱歉，我沒有提供足夠的信息:我們應(yīng)該合并任何連續(xù)和重疊的日期范圍.

UPDATE: Sorry, I didn't provide enough info: we should merge any continuous and overlapping date ranges.

推薦答案

按開始日期排序.

然后迭代并檢查下一個項目的開始日期是在當(dāng)前項目的結(jié)束日期之前還是之后.如果是，則將下一個合并到當(dāng)前一個.然后繼續(xù).

Then iterate through and check for if the next item's start date is before or directly after the current one's end date. If it is, then merge the next one into the current one. Then continue.

這篇關(guān)于組合/合并日期范圍的算法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！