- 메트로 코드 값에 해당하는 값 3개까지만 추출
select ld.STATIONNM,
case when ld.PRVSTATION = null then '[종점]' when trim(ld.prvstation) = '' then '[종점]' else ld.PRVSTATION end PRVSTATION,
case when ld.NXTSTATION = null then '[종점]' when trim(ld.NXTSTATION) = '' then '[종점]' else ld.NXTSTATION end NXTSTATION,
case when ld.PRVMETROCD = null then '9999' when trim(ld.PRVMETROCD) = '' then '9999' else ld.PRVMETROCD end PRVMETROCD,
case when ld.NXTMETROCD= null then '9999' when trim(ld.NXTMETROCD) = '' then '9999' else ld.NXTMETROCD end NXTMETROCD,
bs.BELONGLINENO,
case when LENGTH(tt.HOUR) = 1 then '0' || tt.HOUR else tt.HOUR end HOUR,
tt.TIMETOPRV, tt.TIMETONXT
from METRO_STATN_LINEDETL ld
INNER JOIN METRO_STATN_TIMETBL tt ON ld.METROCD=tt.METROCD
INNER JOIN METRO_STATN_BASIC bs ON bs.fulldistcd=ld.fulldistcd
where ld.METROCD='103' and tt.METROCD='103' and tt.WEEK=1 limit 3
-- 최소시간만 추출
select ld.STATIONNM,
case when ld.PRVSTATION = null then '[종점]' when trim(ld.prvstation) = '' then '[종점]' else ld.PRVSTATION end PRVSTATION,
case when ld.NXTSTATION = null then '[종점]' when trim(ld.NXTSTATION) = '' then '[종점]' else ld.NXTSTATION end NXTSTATION,
case when ld.PRVMETROCD = null then '9999' when trim(ld.PRVMETROCD) = '' then '9999' else ld.PRVMETROCD end PRVMETROCD,
case when ld.NXTMETROCD= null then '9999' when trim(ld.NXTMETROCD) = '' then '9999' else ld.NXTMETROCD end NXTMETROCD,
bs.BELONGLINENO,
case when LENGTH(tt.HOUR) = 1 then '0' || tt.HOUR else tt.HOUR end HOUR,
tt.TIMETOPRV, tt.TIMETONXT
from METRO_STATN_LINEDETL ld
INNER JOIN METRO_STATN_TIMETBL tt ON ld.METROCD=tt.METROCD
INNER JOIN METRO_STATN_BASIC bs ON bs.fulldistcd=ld.fulldistcd
where ld.METROCD='103' and tt.METROCD='103' and tt.WEEK=1
and tt.hour in (
select min(hour) from METRO_STATN_TIMETBL where metrocd=ld.metrocd and week=tt.week
)
-- 최소시간이면서 TIMETOPRV 값이 널값이 아닌 데이터 추출
select ld.STATIONNM,
case when ld.PRVSTATION = null then '[종점]' when trim(ld.prvstation) = '' then '[종점]' else ld.PRVSTATION end PRVSTATION,
case when ld.NXTSTATION = null then '[종점]' when trim(ld.NXTSTATION) = '' then '[종점]' else ld.NXTSTATION end NXTSTATION,
case when ld.PRVMETROCD = null then '9999' when trim(ld.PRVMETROCD) = '' then '9999' else ld.PRVMETROCD end PRVMETROCD,
case when ld.NXTMETROCD= null then '9999' when trim(ld.NXTMETROCD) = '' then '9999' else ld.NXTMETROCD end NXTMETROCD,
bs.BELONGLINENO,
case when LENGTH(tt.HOUR) = 1 then '0' || tt.HOUR else tt.HOUR end HOUR,
tt.TIMETOPRV, tt.TIMETONXT
from METRO_STATN_LINEDETL ld
INNER JOIN METRO_STATN_TIMETBL tt ON ld.METROCD=tt.METROCD
INNER JOIN METRO_STATN_BASIC bs ON bs.fulldistcd=ld.fulldistcd
where ld.METROCD='103' and tt.METROCD='103' and tt.WEEK=1
and tt.hour in (
select min(hour) from METRO_STATN_TIMETBL
where metrocd=ld.metrocd and week=tt.week and trim(TIMETOPRV) <> '' and TIMETOPRV is not null
)
'Study > Oracle' 카테고리의 다른 글
[oracle] 기초교육 (0) | 2019.05.14 |
---|---|
[oracle] 접속 (0) | 2019.05.14 |
[oracle] PROCEDURE (0) | 2019.05.14 |
[oracle] FUNCTION, TRIGGER(미완성), UPDATE, TRUNC, GROUP BY (0) | 2019.05.14 |
[oracle] 비만도 계산 create, insert, update, case when (0) | 2019.05.13 |