php - 以Leg分组，也加入属于最早集合时间的站点

Question

我需要做以下事情。我有 9 条记录由 schedule_id 链接。这 9 条记录被分成 3 组，每组 3 条，例如腿 1、腿 2 和腿 3，所以我有以下内容

order sched leg site   date
1      1     1   1     2014-01-01
2      1     2   34    2014-12-31
3      1     1   15    2014-04-23
4      1     3   4     2014-07-12
5      1     2   89    2014-04-10
6      1     1   13    2014-09-09
7      1     3   9     2014-03-12
8      1     3   10    2014-11-10
9      1     2   11    2014-08-08

在我的 sql 中，我按腿分组，所以我得到了 3 条记录。我需要提取属于每个腿组中最早和最晚日期的网站 ID，然后将我的网站信息表加入到生成的 ID 中，以提取这些网站的公司名称等。

我的腿的sql代码如下

select l.leg_no, 
                  l.start_region, 
                  r.region_name as end_region, 
                  l.rate,
                  sum(x.est_distance) as distance,
                  sum(x.est_weight) as weight,
                  count(x.order_id) as count, 
                  min(x.req_col_time) as earliesttime,
                  sum(x.total_rpb) as total_rpb, 
                  max(x.req_del_time) as latesttime from 
                  (select ord2.* from loads as l1
                  left join orders as ord2 on ord2.load_id = l1.load_id
                  left join debrief_docs as db2 on db2.oid = ord2.order_id
                  where l1.schedule_id = '.$id.'
                  union
                  select ord3.* from loads as l2
                  left join drops as drop1 on drop1.load_id = l2.load_id
                  left join orders as ord3 on ord3.drop_id = drop1.drop_id
                  left join debrief_docs as db3 on db3.oid = ord3.order_id
                  where l2.schedule_id = '.$id.')as x
                  join loads as l on l.schedule_id = '.$id.'
                  join regions as r on r.region_id = l.region_id
                  group by l.leg_no asc

任何关于如何实现这一目标的想法将不胜感激。 ;)谢谢大家

Answer 1

让我们处理问题的重要部分，即获取每个分支组的第一个和最后一个站点 ID。我会为此目的使用变量。以下查询枚举了 leg id:

  select l.*,
         (@rn := if(@l = leg_id, @rn + 1,
                    if(@l := leg_id, 1, 1)
                   )
         ) as seqnum
  from loads l cross join
       (select @rn := 0, @l := 0) vars
  order by schedule_id, leg_id, date;

下面总结了记录并产生了两列，第一个和最后一个站点:

select schedule_id, leg_id,
       max(case when seqnum = 1 then site_id end) as first_site,
       max(case when seqnum = 3 then site_id end) as last_site
from (select l.*,
             (@rn := if(@l = leg_id, @rn + 1,
                        if(@l := leg_id, 1, 1)
                       )
             ) as seqnum
      from loads l cross join
           (select @rn := 0, @l := 0) vars
      order by schedule_id, leg_id, date
     ) l
group by schedule_id, leg_id;

然后您可以加入其余表格以获取所需的其他信息。