数据合并到新表

Question

有表结构及数据如下:
ID  callsign   start   dest  startTime arrTime reg
1    a1         abc     cde    0800     0900   2541
2    b1         cde     mnq    1000     1034   2541
3    c1         xyz     cde    0823     0822   2877
4    c2         cde     wsx    0834     0933   2877
5    e1         iur     cde    1234     1543   2877
6    e1         cde     mop    1600     1800   2877
....  
上面记录的数据的第一条的含义是一个航班a1,从一个城市(abc)出发,到达另一个城市(cde),abc出发时间是0800,到达cde的时间是0900,机号是2541,第二条数据的含义是航班a2,从城市cde出发,时间为1000,到达mnq,落地时间是1034,
现在要将上面的数据合并,根据不同的机号,得到以下的表:
id    callsign   reg    arrTime  startTime  rout
1     a1/b1      2541   0900        1034   abc-cde-mnq
2     c1/c2      2877   0822        0834   xyz-cde-wsx
3     e1         2877   1543        1600   iur-cde-mop
...其中如果两个航班的航班号是一致的,航班号不变,否则将两个航班号用"/"号隔开;合并的原则是取相同机号的最近的时间为一个合并数据
请问该如何写这个语句?

Answer 1

id    callsign   reg    arrTime  startTime  rout
1     a1/b1      2541   0900        1034   abc-cde-mnq
2     c1/c2      2877   0822        0834   xyz-cde-wsx
3     e1         2877   1543        1600   iur-cde-mop
上面结果不对吧?
应该为:
id    callsign   reg    arrTime  startTime  rout
1     a1/b1      2541   0900        1034   abc-cde-mnq
2     c1/c2      2877   0822        0933   xyz-cde-wsx
3     e1         2877   1543        1800   iur-cde-mop

Answer 2

不好意思,合并的结果应该是:
id    callsign   reg    arrTime  startTime  rout
1     a1/b1      2541   0900        1000   abc-cde-mnq
2     c1/c2      2877   0822        0834   xyz-cde-wsx
3     e1         2877   1543        1600   iur-cde-mop
就是取前一个航班到达城市cde的时间和下一个由cde出发的航班的时间合并起来.

Answer 3

有意义,我要求得航班在城市cde的停场时间,并且要明确他们之间的关系

Answer 4

你有四个机号都是2877,好难搞.我改后面两个为2878.if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
ID        int,  
callsign  varchar(10),
start     varchar(10),
dest      varchar(10),
startTime varchar(10),
arrTime   varchar(10),
reg       varchar(10)
)insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(1,'a1','abc','cde','0800','0900','2541')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(2,'b1','cde','mnq','1000','1034','2541')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(3,'c1','xyz','cde','0823','0822','2877')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(4,'c2','cde','wsx','0834','0933','2877')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(5,'e1','iur','cde','1234','1543','2878')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(6,'e1','cde','mop','1600','1800','2878')if object_id('pubs..tb1') is not null
   drop table tb1
go
if object_id('pubs..tb2') is not null
   drop table tb2
goselect * into tb1 from tb where reg in
(
  select reg from
  (
    select distinct reg , callsign from tb
  )   t1
  group by reg 
  having count(*) = 2
)select * into tb2 from tb where reg in
(
  select reg from
  (
    select distinct reg , callsign from tb
  )   t1
  group by reg 
  having count(*) = 1
)select tb11.callsign + '/' + tb12.callsign as callsign ,tb11.reg , tb11.arrtime , tb12.arrtime as starttime , tb11.start + '-' + tb11.dest + '-' + tb12.dest as rout from
(
select tb1.* from tb1,
(select reg , min(arrtime) as arrtime from tb1 group by reg) bmin
where tb1.reg = bmin.reg and tb1.arrtime = bmin.arrtime
) tb11,
(
select tb1.* from tb1,
(select reg , max(arrtime) as arrtime from tb1 group by reg) bmax
where tb1.reg = bmax.reg and tb1.arrtime = bmax.arrtime
) tb12
where tb11.reg = tb12.reg
union all
select tb21.callsign + '/' + tb22.callsign as callsign ,tb21.reg , tb21.arrtime , tb22.arrtime as starttime , tb21.start + '-' + tb21.dest + '-' + tb22.dest as rout from
(
select tb2.* from tb2,
(select reg , min(arrtime) as arrtime from tb2 group by reg) bmin
where tb2.reg = bmin.reg and tb2.arrtime = bmin.arrtime
) tb21,
(
select tb2.* from tb2,
(select reg , max(arrtime) as arrtime from tb2 group by reg) bmax
where tb2.reg = bmax.reg and tb2.arrtime = bmax.arrtime
) tb22
where tb21.reg = tb22.regdrop table tb
drop table tb1
drop table tb2
callsign              reg        arrtime    starttime  rout       
--------------------- ---------- ---------- ---------- -----------
a1/b1                 2541       0900       1034       abc-cde-mnq
c1/c2                 2877       0822       0933       xyz-cde-wsx
e1/e1                 2878       1543       1800       iur-cde-mop（所影响的行数为 3 行）

Answer 5

你得数据改了?
id    callsign   reg    arrTime  startTime  rout
1     a1/b1      2541   0900        1000   abc-cde-mnq
2     c1/c2      2877   0822        0834   xyz-cde-wsx
3     e1         2877   1543        1600   iur-cde-mop
if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
ID        int,  
callsign  varchar(10),
start     varchar(10),
dest      varchar(10),
startTime varchar(10),
arrTime   varchar(10),
reg       varchar(10)
)insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(1,'a1','abc','cde','0800','0900','2541')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(2,'b1','cde','mnq','1000','1034','2541')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(3,'c1','xyz','cde','0823','0822','2877')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(4,'c2','cde','wsx','0834','0933','2877')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(5,'e1','iur','cde','1234','1543','2878')
insert into tb(ID,callsign,start,dest,startTime,arrTime,reg) values(6,'e1','cde','mop','1600','1800','2878')if object_id('pubs..tb1') is not null
   drop table tb1
go
if object_id('pubs..tb2') is not null
   drop table tb2
goselect * into tb1 from tb where reg in
(
  select reg from
  (
    select distinct reg , callsign from tb
  )   t1
  group by reg 
  having count(*) = 2
)select * into tb2 from tb where reg in
(
  select reg from
  (
    select distinct reg , callsign from tb
  )   t1
  group by reg 
  having count(*) = 1
)select tb11.callsign + '/' + tb12.callsign as callsign ,tb11.reg , tb11.arrtime , tb12.startTime , tb11.start + '-' + tb11.dest + '-' + tb12.dest as rout from
(
select tb1.* from tb1,
(select reg , min(arrtime) as arrtime from tb1 group by reg) bmin
where tb1.reg = bmin.reg and tb1.arrtime = bmin.arrtime
) tb11,
(
select tb1.* from tb1,
(select reg , max(startTime) as startTime from tb1 group by reg) bmax
where tb1.reg = bmax.reg and tb1.startTime = bmax.startTime
) tb12
where tb11.reg = tb12.reg
union all
select tb21.callsign ,tb21.reg , tb21.arrtime , tb22.startTime , tb21.start + '-' + tb21.dest + '-' + tb22.dest as rout from
(
select tb2.* from tb2,
(select reg , min(arrtime) as arrtime from tb2 group by reg) bmin
where tb2.reg = bmin.reg and tb2.arrtime = bmin.arrtime
) tb21,
(
select tb2.* from tb2,
(select reg , max(startTime) as startTime from tb2 group by reg) bmax
where tb2.reg = bmax.reg and tb2.startTime = bmax.startTime
) tb22
where tb21.reg = tb22.regdrop table tb
drop table tb1
drop table tb2
callsign              reg        arrtime    startTime  rout       
--------------------- ---------- ---------- ---------- -----------
a1/b1                 2541       0900       1000       abc-cde-mnq
c1/c2                 2877       0822       0834       xyz-cde-wsx
e1                    2878       1543       1600       iur-cde-mop（所影响的行数为 3 行）