php – 组合顺序日期

我的表中有以下数据

在shift_id的基础上,我想结合转换日期.

总之,我想要以下输出：

**output format : FromDate To ToDate (Shift_id)**

2012-12-01 To 2012-12-02 (2)

2012-12-03 (1)

2012-12-04 (2)

2012-12-18 To 2012-12-20 (1)

2012-12-21 To 2012-12-22 (2)

2012-12-23 To 2012-12-24 (1)

2012-12-25 To 2012-12-30 (2)

我尝试了几件事,但没有运气.任何人请帮助/指导我解决这个问题.

我写了这段代码

    while($row_shift = MysqL_fetch_object($result_shift)) {
    $new_date = $row_shift->shift_date;
    $new_shift = $row_shift->shift_id;
    if($new_shift !== $old_shift) {
        $shift_name = $new_shift;
        $shift_date_1 =  date('D d M',strtotime($new_date))." (".$shift_name.")";               
    } else {
        $shift_name = $old_shift;
        $shift_date_1 =  date('D d M',strtotime($old_date))." (".$shift_name.")";
        $diff = abs(floor( (strtotime($new_date) - strtotime($old_date)) / 3600 / 24));
        if($diff == 1) {                    
            $to_part = ' To ';
            $shift_date_2 = date('D d M',strtotime($new_date))." (".$shift_name.")";

        } else {
            $to_part = ' To ';
            $shift_date_2 = date('D d M',strtotime($old_date))." (".$shift_name.")";    
        }
    }

    $s_d_2 = $to_part.$shift_date_2;

    $shift_date[] = $shift_date_1.$s_d_2;

    $old_date = $row_shift->shift_date;
    $old_shift = $row_shift->shift_id;

}
echo implode("<br/>", $shift_date);

并输出此代码

Output : 
Sat 01 Dec (2)
Sat 01 Dec (2) To Sun 02 Dec (2)
Mon 03 Dec (1)Sun 02 Dec (2)
Tue 04 Dec (2)Sun 02 Dec (2)
Tue 18 Dec (1)Sun 02 Dec (2)
Tue 18 Dec (1) To Wed 19 Dec (1)
Wed 19 Dec (1) To Thu 20 Dec (1)
Fri 21 Dec (2)Thu 20 Dec (1)
Fri 21 Dec (2) To Sat 22 Dec (2)
Sun 23 Dec (1)Sat 22 Dec (2)
Sun 23 Dec (1) To Mon 24 Dec (1)
Tue 25 Dec (2)Mon 24 Dec (1)
Tue 25 Dec (2) To Wed 26 Dec (2)
Wed 26 Dec (2) To Thu 27 Dec (2)
Thu 27 Dec (2) To Fri 28 Dec (2)
Fri 28 Dec (2) To Sat 29 Dec (2)
Sat 29 Dec (2) To Sun 30 Dec (2)

谢谢.

解决方法:

这是一个SQLFiddle demo

select min(shift_date) start_data,max(shift_date) end_date,
          min(Shift_id) Shift_id
from
(
select t.*,if(shift_id<>@CurShift,@i:=@i+1,@i) Group_num,
       @CurShift:=shift_id 
from t, (select @CurShift:=0,@i:=0) t1 
order by shift_date
 ) b

group by Group_num
order by Group_num

（编辑：北几岛）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!