데이터베이스 질문과 답변
안녕하세요. SQL을 문의 드립니다.
A테이블에 A, B,C 가 있고
B테이블에 A1, A2, A3, B1,B2,B3, C1,C2,C3 있다고 할때
SQL 하나로
A, A3 (A중에가 가장큰값)
B, B3 (B중에가 가장큰값)
C, C3 (C중에가 가장큰값)
이렇게 가져오려고 합니다.
아시는분은 답글 좀 부탁드립니다.
by 손님
[2012.03.22 18:10:21]
GREATEST(A1,A2,A3)
by 손님
[2012.03.22 18:11:20]
1 | <div>with TABLE_A as<div>(<div> select 'A' as COL1 from dual union all<div> select 'B' from dual union all<div> select 'C' from dual<div>), TABLE_B as<div>(<div> select 'A1' as col2 from dual union all<div> select 'A2' from dual union all<div> select 'A3' from dual union all<div> select 'B2' from dual union all<div> select 'B1' from dual union all<div> select 'C1' from dual<div>) <div>select <div> ta.col1,<div> (select max(col2) from table_b tb where tb.col2 like ta.col1 || '%') as col2<div>from <div> table_a ta;</div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div> |
by 손님
[2012.03.22 21:41:53]
-- 쉽게 적용 할 수 있습니다.
WITH a_table AS
WITH a_table AS
(SELECT 'A' AS cd
FROM dual
UNION ALL
SELECT 'B' AS cd
FROM dual
UNION ALL
SELECT 'C' AS cd
FROM dual )
, b_table AS
(SELECT 'A1' AS cd
FROM dual
UNION ALL
SELECT 'A2' AS cd
FROM dual
UNION ALL
SELECT 'A3' AS cd
FROM dual
UNION ALL
SELECT 'B1' AS cd
FROM dual
UNION ALL
SELECT 'B2' AS cd
FROM dual
UNION ALL
SELECT 'B3' AS cd
FROM dual
UNION ALL
SELECT 'C1' AS cd
FROM dual
UNION ALL
SELECT 'C2' AS cd
FROM dual
UNION ALL
SELECT 'C3' AS cd
FROM dual )
SELECT cd
,vlu
FROM (
SELECT a.cd
,b.cd AS vlu
,row_number() OVER (PARTITION BY a.cd ORDER BY b.cd DESC) AS ranking
답변 감사합니다.
샘플을 가지고 sql을 작성해보니 잘 되네요.
많은 도움이 되었습니다.
FROM a_table a
,b_table b
WHERE b.cd LIKE a.cd||'%' )
WHERE ranking = 1
by 케빈
[2012.03.23 09:59:08]
답변 감사합니다.
샘플을 가지고 sql을 작성해보니 잘 되네요.
많은 도움이 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.