select 가공일시,노선번호, 정류소명, BITID,정류소단축ID,정류소ID,버스노선ID,차량번호,버스ID,잔여정류소,도착예정시간
from (select
TB_ISH_BITBUSARRIV.PROCDT as 가공일시,
TB_ADM_BUSROUTE.ROUTENO as 노선번호,
TB_ADM_BUSSTOP.BSTOPNM as 정류소명,
TB_ISH_BITBUSARRIV.BITID as BITID,
TB_ADM_BUSSTOP.SHORT_BSTOPID as 정류소단축ID,
TB_ADM_BUSSTOP.BSTOPID aS 정류소ID,
TB_ISH_BITBUSARRIV.ROUTEID as 버스노선ID,
TB_ADM_BUS.CARREGNO as 차량번호,
TB_ISH_BITBUSARRIV.BUSID as 버스ID,
TB_ISH_BITBUSARRIV.REST_BSTOPCNT as 잔여정류소,
TRUNC(TB_ISH_BITBUSARRIV.ARRPLANTM/60)||'분'||MOD(TB_ISH_BITBUSARRIV.ARRPLANTM,60)||'초'as 도착예정시간
from
TB_ISH_BITBUSARRIV,
TB_ADM_BUSROUTE,
TB_ADM_BUS,
TB_OMM_BIT,
TB_ADM_BUSSTOP
where TB_ISH_BITBUSARRIV.ROUTEID = TB_ADM_BUSROUTE.ROUTEID
and TB_ISH_BITBUSARRIV.BUSID = TB_ADM_BUS.BUSID
and TB_ISH_BITBUSARRIV.BITID = TB_OMM_BIT.BITID
and TB_OMM_BIT.BSTOPID = TB_ADM_BUSSTOP.BSTOPID
and PROCDT between to_date('2019-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2019-03-01 23:59:59','YYYY-MM-DD HH24:MI:SS')
and TB_ADM_BUSSTOP.SHORT_BSTOPID = 36107
and TB_ISH_BITBUSARRIV.BUSID = 7013850
and TB_ADM_BUSROUTE.ROUTEID = '165000014'
and TRUNC(TB_ISH_BITBUSARRIV.ARRPLANTM/60)||'분'||MOD(TB_ISH_BITBUSARRIV.ARRPLANTM,60)||'초' = '0분0초'
order by 5,1,11)
group by 가공일시,노선번호, 정류소명, BITID,정류소단축ID,정류소ID,버스노선ID,차량번호,버스ID,잔여정류소,도착예정시간
order by 1
안녕하세요. 해결책이 생각이 안나서 질문좀 드립니다. 도움 부탁드려요
결과갑이 가공일시를 제외하고 모두 같습니다. 8개의 결과값중에서 40초이내차이나는건
하나의 결과값으로만 표출되게 할수가 있나요 그렇게 4개의 값만이 나와야하는데
결과값 그림처럼 4개의 그룹중에 아무것도 상관없이 각1개씩만 나오면 되는데 기준을 잡아야하는게 가공일시뿐이여서 너무 어렵네요 ㅠㅠ
SELECT * FROM (SELECT a.procdt AS 가공일시 , b.routeno AS 노선번호 , e.bstopnm AS 정류소명 , a.bitid AS BITID , e.short_bstopid AS 정류소단축ID , e.bstopid AS 정류소ID , a.routeid AS 버스노선ID , c.carregno AS 차량번호 , a.busid AS 버스ID , a.rest_bstopcnt AS 잔여정류소 , TRUNC(a.arrplantm/60)||'분'||MOD(a.arrplantm,60)||'초' AS 도착예정시간 , COUNT(*) OVER(ORDER BY a.procdt RANGE BETWEEN INTERVAL '40' SECOND PRECEDING AND CURRENT ROW) cnt FROM tb_ish_bitbusarriv a , tb_adm_busroute b , tb_adm_bus c , tb_omm_bit d , tb_adm_busstop e WHERE a.routeid = b.routeid AND a.busid = c.busid AND a.bitid = d.bitid AND d.bstopid = e.bstopid AND a.procdt >= TO_DATE('2019-03-01', 'yyyy-mm-dd') AND a.procdt < TO_DATE('2019-03-01', 'yyyy-mm-dd') + 1 AND e.short_bstopid = 36107 AND a.busid = 7013850 AND a.routeid = 165000014 AND a.arrplantm = 0 ) WHERE cnt = 1 ;