进来看下这个sql怎么写

[-一个大坑]

select n.deriveno,
       n.appno,
       m.ride_starttime,
       max(decode(c.stagecode, '4', c.mileage, null)) mileage1,
       max(decode(c.stagecode, '7', c.mileage, null)) mileage2
  from app_send s, app_nomapping n, app_main m, app_carstatus c
 where s.deriveno = n.deriveno
   and n.optype = 'CMB'
   and n.appno = m.appno
   and s.deriveno = c.deriveno
   and n.appno not in (select r.deriveno from app_ridecost r)
 group by n.deriveno, n.appno, m.ride_starttime
 order by n.deriveno, m.ride_starttime

这是查询语句与查询结果，下面是我要的效果
我按旧单号（deriveno）+时间排序。旧单号一样，时间早的取mileage1，时间晚的取mileage2（appno是拆单的单号，一个单只能拆成两个）

[-一个大坑]

引用 3 楼 qq646748739 的回复:

试试这个：

select deriveno,appno,ride_starttime, decode(rn,1,mileage1,mileage2) as mileage
  from (select deriveno,appno, ride_starttime,mileage1,mileage2,row_number()over(partition by deriveno order by ride_starttime) rn
          from (select n.deriveno,
					   n.appno,
					   m.ride_starttime,
					   max(decode(c.stagecode, '4', c.mileage, null)) mileage1,
					   max(decode(c.stagecode, '7', c.mileage, null)) mileage2
				  from app_send s, app_nomapping n, app_main m, app_carstatus c
				 where s.deriveno = n.deriveno
				   and n.optype = 'CMB'
				   and n.appno = m.appno
				   and s.deriveno = c.deriveno
				   and n.appno not in (select r.deriveno from app_ridecost r)
				 group by n.deriveno, n.appno, m.ride_starttime
			   )
         );
 

可以了，谢谢

[碧水幽幽泉]

时间早和时间晚是相对什么讲的？即：分别什么时候取晚的？

[-一个大坑]

引用 1 楼 qq646748739 的回复:

时间早和时间晚是相对什么讲的？即：分别什么时候取晚的？

针对原单号（deriveno）按时间（ride_starttime）排序。排序我已经排好了 比如CMB201707270001拆成两张单 APP201707270011取10:00和mileage1的值 APP201707270009取11:00和mileage2的值

[碧水幽幽泉]

试试这个：

select deriveno,appno,ride_starttime, decode(rn,1,mileage1,mileage2) as mileage
  from (select deriveno,appno, ride_starttime,mileage1,mileage2,row_number()over(partition by deriveno order by ride_starttime) rn
          from (select n.deriveno,
					   n.appno,
					   m.ride_starttime,
					   max(decode(c.stagecode, '4', c.mileage, null)) mileage1,
					   max(decode(c.stagecode, '7', c.mileage, null)) mileage2
				  from app_send s, app_nomapping n, app_main m, app_carstatus c
				 where s.deriveno = n.deriveno
				   and n.optype = 'CMB'
				   and n.appno = m.appno
				   and s.deriveno = c.deriveno
				   and n.appno not in (select r.deriveno from app_ridecost r)
				 group by n.deriveno, n.appno, m.ride_starttime
			   )
         );