데이터베이스 질문과 답변
아래 sql문장이 존재할 때 현재 결과는 한줄 [ SEOUTL, 동대문구, 10] 만 조회 됩니다.
제가 원하는결과는 서울에 상당히 많은 구(AREA)가 존재하는데요.
동대문구는 10, 서대문구는 15, 나머지 모든 구는 30이라는 값을 부여하고 싶은거죠.
그래서 결과가 아래처럼 나오고 싶습니다. 부탁 합니다.
[ SEOUL, 동대문구, 10 ]
[ SEOUL, 송파구, 30 ]
[ SEOUL, 영등포구, 30 ]
WITH AA AS(
SELECT 'SEOUL' AS CITY, '동대문구' AS AREA, 10 AS RATE UNION ALL
SELECT 'SEOUL' AS CITY, '서대문구' AS AREA, 15 AS RATE UNION ALL
SELECT 'SEOUL' AS CITY, NULL AS AREA, 30 AS RATE UNION ALL
SELECT 'BUSAN' AS CITY, '남구' AS AREA, 15 AS RATE UNION ALL
SELECT 'BUSAN' AS CITY, NULL AS AREA, 20 AS RATE
),
BB AS
(
SELECT 'SEOUL' AS CITY, '동대문구' AS AREA UNION ALL
SELECT 'SEOUL' AS CITY, '송파구' AS AREA UNION ALL
SELECT 'SEOUL' AS CITY, '영등포구' AS AREA
)
SELECT BB.CITY, BB.AREA, AA.RATE
FROM AA INNER JOIN BB
ON ( AA.CITY = BB.CITY
AND AA.AREA = BB.AREA
)
;
WITH AA AS(
SELECT 'SEOUL' AS CITY, '동대문구' AS AREA, 10 AS RATE FROM dual UNION ALL
SELECT 'SEOUL' AS CITY, '서대문구' AS AREA, 15 AS RATE FROM dual UNION ALL
SELECT 'SEOUL' AS CITY, NULL AS AREA, 30 AS RATE FROM dual UNION ALL
SELECT 'BUSAN' AS CITY, '남구' AS AREA, 15 AS RATE FROM dual UNION ALL
SELECT 'BUSAN' AS CITY, NULL AS AREA, 20 AS RATE FROM dual
),
BB AS
(
SELECT 'SEOUL' AS CITY, '동대문구' AS AREA FROM dual UNION ALL
SELECT 'SEOUL' AS CITY, '송파구' AS AREA FROM dual UNION ALL
SELECT 'SEOUL' AS CITY, '영등포구' AS AREA FROM dual
)
SELECT BB.CITY, BB.AREA, NVL(AA.RATE, 30)
FROM AA, BB
WHERE AA.CITY(+) = BB.CITY
AND AA.AREA(+) = BB.AREA
WITH aa AS
(
SELECT 'SEOUL' city, '동대문구' area, 10 rate
UNION ALL SELECT 'SEOUL', '서대문구', 15
UNION ALL SELECT 'SEOUL', NULL , 30
UNION ALL SELECT 'BUSAN', '남구' , 15
UNION ALL SELECT 'BUSAN', NULL , 20
)
, bb AS
(
SELECT 'SEOUL' city, '동대문구' area
UNION ALL SELECT 'SEOUL', '송파구'
UNION ALL SELECT 'SEOUL', '영등포구'
)
SELECT b.city
, b.area
, ISNULL(a1.rate, a2.rate) rate
FROM bb b
LEFT OUTER JOIN aa a1
ON a1.city = b.city
AND a1.area = b.area
LEFT OUTER JOIN aa a2
ON a2.city = b.city
AND a2.area IS NULL
;
SELECT b.city
, b.area
, ISNULL( MIN(CASE WHEN a.area = b.area THEN a.rate END)
, MIN(CASE WHEN a.area IS NULL THEN a.rate END)
)
FROM bb b
LEFT OUTER JOIN aa a
ON b.city = a.city
AND b.area = ISNULL(a.area, b.area)
GROUP BY b.city, b.area
;
참 쉽게 해결해주시네요.
sql을 보니 이해하기도 너무 쉽네요. 모르면 어려운데...ㅎㅎ
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.