select
a.*,b.*
from
(
select 10 as ordno from dual
union all
select 20 as ordno from dual
union all
select 30 as ordno from dual
) a,
(
select
a.ordno,a.seq,
(case a.seq when
(
select
min(b.seq)
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
)b
)
then a.price||''
else ''
end
) as price
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
)a
) b
where a.ordno = b.ordno
;
select
a.ordno,a.seq,
(case a.seq
when
(
select
min(b.seq)
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
)b
)
then a.price||''
-- else ''
end
) as price
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
)a
;
--####################################
-- seq 가 적은곳에 가격)
select
a.ordno,a.seq,
decode(a.seq,
(
select
min(b.seq)
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
union all
select 20 as ordno, 10 as seq, 1000 as price from dual
union all
select 20 as ordno, 12 as seq, 3000 as price from dual
union all
select 20 as ordno, 3 as seq, 2000 as price from dual
)b
WHERE
b.ordno = a.ordno
)
,
a.price
,''
) as price
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 3000 as price from dual
union all
select 10 as ordno, 3 as seq, 2000 as price from dual
union all
select 20 as ordno, 10 as seq, 1000 as price from dual
union all
select 20 as ordno, 12 as seq, 3000 as price from dual
union all
select 20 as ordno, 3 as seq, 2000 as price from dual
)a
;
--#######################################################################
-- 이전행 다음행 비교
select -- A.con1 as Acon1,a.price as APrice , A.ordno as Aordno,
B.con1, B.ordno , B.seq ,A.ordno as AAA,B.ordno as BBB, decode( A.ordno, B.ordno ,'',B.price)as price --, ,B.con2
from
(
select ROWNUM as con1, ordno, seq, price
--decode(a.p
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 1000 as price from dual
union all
select 10 as ordno, 3 as seq, 1000 as price from dual
union all
select 20 as ordno, 10 as seq, 3000 as price from dual
union all
select 20 as ordno, 12 as seq, 3000 as price from dual
union all
select 20 as ordno, 3 as seq, 3000 as price from dual
)
)A ,
(
select ROWNUM as con1, ordno, seq,price, ROWNUM-1 as con2
--decode(a.p
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 1000 as price from dual
union all
select 10 as ordno, 3 as seq, 1000 as price from dual
union all
select 20 as ordno, 10 as seq, 3000 as price from dual
union all
select 20 as ordno, 12 as seq, 3000 as price from dual
union all
select 20 as ordno, 3 as seq, 3000 as price from dual
)
)B
where A.con1(+) = B.con2
order by con1
;
--###############################################################
--LAG 함수이용
select
ordno
, seq
,price
,LAG(ordno) OVER(order by ordno) as ordno2
,decode(ordno,LAG(ordno)OVER(order by ordno) ,'',price)as price2
from
(
select 10 as ordno, 2 as seq, 1000 as price from dual
union all
select 10 as ordno, 5 as seq, 1000 as price from dual
union all
select 10 as ordno, 3 as seq, 1000 as price from dual
union all
select 20 as ordno, 10 as seq, 3000 as price from dual
union all
select 20 as ordno, 12 as seq, 3000 as price from dual
union all
select 20 as ordno, 3 as seq, 3000 as price from dual
)
;
--##################################################
--# 이전번호 다음번호 현재번호 조회
-- DB 다음행 , 이전행 현재행 값 조회
#
LAG(컬럼) OVER ( order by 컬럼) 이전 컬럼의값,
LEAD(컬럼) OBER (order by 컬럼) 다음 컬럼의 값
select
LAG(ordno) over (order by ordno) 이전주문번호,
ordno 주문번호,
LEAD(ordno) OVER (order by ordno) 다음주문번호
FROM
(
select 10 as ordno from dual
union all
select 10 as ordno from dual
union all
select 10 as ordno from dual
union all
select 20 as ordno from dual
union all
select 20 as ordno from dual
union all
select 20 as ordno from dual
)
;
'언어 > DB' 카테고리의 다른 글
| [PLSQL] (0) | 2015.03.10 |
|---|---|
| [DB] 문법 (0) | 2015.03.09 |
| [프로시저] (0) | 2015.02.24 |
| [DB] 시노님 (0) | 2015.01.01 |
| 20140530 [SQL] (0) | 2014.12.22 |