select ts.lotno,min(ts.starttime)starttime,max(ts.checkouttime) checkouttime,ts.opno from (select t.lotno, t.starttime, t.checkouttime, t.opno, case when to_date(lag(t.checkouttime) over(partition by t.lotno, t.opno order by t.checkouttime)) - t.starttime = -1 then 0 when to_date(lag(t.starttime) over(partition by t.lotno, t.opno order by t.starttime)) - t.starttime = 1 then 0 else row_number() over(order by t.lotno, t.starttime, t.checkouttime, t.opno) end ts from AAA t )ts group by ts.lotno,ts.opno
select lotno ,min(starttime ) fristtime,max( checkouttime ) lasttime ,opno
from aaa
where opno='A110'
group by lotno,opno是这个意思吗?
lotno fristtime lasttime opno
AM2015111 2015/3/9 16:44:27 2015/3/9 16:45:45 A110
AM2015111 2015/3/9 18:05:09 2015/3/9 18:32:22 A110
跟直接查询没有区别,不信你可以试下
如果实际执行真的是这样,检查lotno,是否包含不同数量的空格之类的,导致两条记录中的Lotno不一致
lotno fristtime lasttime opno
AM2015111 2015/3/9 16:44:27 2015/3/9 16:45:45 A110
AM2015111 2015/3/9 18:05:09 2015/3/9 18:32:22 A110
跟直接查询没有区别,不信你可以试下怎么可能?! 照你的意oracle的聚合函数和group by就是一句废话了?
t.starttime,
t.checkouttime,
t.opno,
case
when to_date(lag(t.checkouttime)
over(partition by t.lotno,
t.opno order by t.checkouttime)) -
t.starttime = -1 then
0
when to_date(lag(t.starttime)
over(partition by t.lotno,
t.opno order by t.starttime)) -
t.starttime = 1 then
0
else
row_number()
over(order by t.lotno, t.starttime, t.checkouttime, t.opno)
end ts
from AAA t )ts
group by ts.lotno,ts.opno