데이터베이스 질문과 답변
데이터
KEY MONEY CHK
5 700 B
5 0 A
6 0 B
6 0 A
7 300 B
8 0 A
데이터 에서 뽑고 싶은건
KEY MONEY CHK
5 700 B
6 0 B
7 300 B
8 0 A
위처럼 KEY 별로 중복을 제거 해야되구요
CHK B 에 있는거를 쭉 뿌리고. 중복이 아닐경우 A를 뿌려야됩니다
방법좀 가르쳐 주세요 ㅠㅠ
by madcat
[2008.12.08 19:07:57]
KEY, CHK가 UNIQUE하다면...
WITH TBL
AS
(
SELECT 5 KEY, 700 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 5 KEY, 0 MONEY, 'A' CHK FROM DUAL UNION ALL
SELECT 6 KEY, 0 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 6 KEY, 0 MONEY, 'A' CHK FROM DUAL UNION ALL
SELECT 7 KEY, 300 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 8 KEY, 0 MONEY, 'A' CHK FROM DUAL
)
SELECT KEY, SUBSTR(MAX(CHK||MONEY), 2) MONEY, MAX(CHK) CHK
FROM TBL
GROUP BY KEY
ORDER BY KEY
WITH TBL
AS
(
SELECT 5 KEY, 700 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 5 KEY, 0 MONEY, 'A' CHK FROM DUAL UNION ALL
SELECT 6 KEY, 0 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 6 KEY, 0 MONEY, 'A' CHK FROM DUAL UNION ALL
SELECT 7 KEY, 300 MONEY, 'B' CHK FROM DUAL UNION ALL
SELECT 8 KEY, 0 MONEY, 'A' CHK FROM DUAL
)
SELECT KEY, SUBSTR(MAX(CHK||MONEY), 2) MONEY, MAX(CHK) CHK
FROM TBL
GROUP BY KEY
ORDER BY KEY
by 도가니
[2008.12.08 19:11:27]
with abc as(
select 5 key, 700 money, 'B' chk from dual union all
select 5 key, 0 money, 'A' chk from dual union all
select 6 key, 0 money, 'B' chk from dual union all
select 6 key, 0 money, 'A' chk from dual union all
select 7 key, 300 money, 'B' chk from dual union all
select 8 key, 0 money, 'A' chk from dual
)
select * from (
select a.*, rank() over(partition by key order by chk desc) rk from abc a)
where rk=1
;
select 5 key, 700 money, 'B' chk from dual union all
select 5 key, 0 money, 'A' chk from dual union all
select 6 key, 0 money, 'B' chk from dual union all
select 6 key, 0 money, 'A' chk from dual union all
select 7 key, 300 money, 'B' chk from dual union all
select 8 key, 0 money, 'A' chk from dual
)
select * from (
select a.*, rank() over(partition by key order by chk desc) rk from abc a)
where rk=1
;
by 손님
[2008.12.08 19:40:31]
두분다 답변 정말 감사 드려요!
) 버튼을 클릭하여 작성 하시면 됩니다.