하루에 하나씩 Codewar에서 SQL 문제를 풀고 있다. 그 중 필요한 부분은 기록으로 남긴다.
1) COALESCE / NullIF 사용시
- NULLIF(exp1, exp2): exp1값과 exp2값이 동일하면 NULL을 그렇지 않으면 exp1을 반환
- COALESCE(expr1,expr2,expr3,…): expr1이 NULL이 아니면 expr1값을, 그렇지 않으면 COALESCE(expr2,expr3,…)값을 반환
|
1 2 3 4 5 6 7 |
SELECT RANK() OVER (ORDER BY SUM(points) DESC) AS rank, COALESCE(NULLIF(clan,''), '[no clan specified]') AS clan, SUM(points) AS total_points, COUNT(name) AS total_people FROM people GROUP BY clan |
2) Case-When 구문
|
1 2 3 4 5 6 |
select rank() over (order by sum(points) desc), CASE WHEN clan = '' THEN '[no clan specified]' ELSE clan END, sum(points) total_points, count(name) total_people from people group by clan |
3) With를 통한 임시 테이블 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
WITH t1 AS ( SELECT clan, SUM(points) AS total_points, COUNT(name) AS total_people FROM people WHERE clan <> '' GROUP BY clan ) SELECT ROW_NUMBER() OVER (ORDER BY t2.total_points DESC) AS rank, COALESCE(NULLIF(t2.clan, ''), '[no clan specified]') AS clan, t2.total_points, t2.total_people FROM ( SELECT clan, total_points, total_people FROM t1 UNION ALL SELECT clan, points AS total_points, 1 AS total_people FROM people WHERE clan = '' ) AS t2 ORDER BY rank |