Challenges

case when 사용?

SELECT
t.hacker_id,
h2.name,
t.challenges_created


FROM (
    SELECT h.hacker_id AS hacker_id, 
    COUNT(*) AS challenges_created
    FROM Hackers h
    JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
) t
JOIN Hackers h2 ON t.hacker_id = h2.hacker_id

ORDER BY t.challenges_created DESC, t.hacker_id
;
SQL
복사

SELECT
t.hacker_id,
h2.name,
t.challenges_created


FROM (
    SELECT h.hacker_id AS hacker_id, 
    COUNT(*) AS challenges_created
    FROM Hackers h
    JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
) t
JOIN Hackers h2 ON t.hacker_id = h2.hacker_id


ORDER BY t.challenges_created DESC, t.hacker_id
;
SQL
복사

•

having 절 clause

-- 해커별 챌린지 생산 개수 중 최대값
SELECT MAX(challenges_created)
FROM (
SELECT hacker_id
, COUNT(*) AS challenges_created
FROM Challenges
GROUP BY hacker_id) sub
SQL
복사

-- hacker 별 생성한 challenge 개수
-- 해당 챌린지 개수별 단독 점유 갯수
SELECT challenges_created
FROM (
SELECT hacker_id
	, COUNT(*) AS challenges_created
FROM Challenges
GROUP BY hacker_id
) sub
GROUP BY challenges_created
HAVING COUNT(*) = 1
SQL
복사

서브 쿼리 문제 풀이 정리

SELECT h.hacker_id
	, h.name
	, COUNT(*) challenges_created
FROM Challenges c
	INNER JOIN Hackers h ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name

HAVING challenges_created = (
					SELECT MAX(challenges_created)
					FROM (
					SELECT hacker_id
					, COUNT(*) AS challenges_created
					FROM Challenges
					GROUP BY hacker_id) sub
)

OR challenges_created IN (
				SELECT challenges_created
				FROM (
				SELECT hacker_id
					, COUNT(*) AS challenges_created
				FROM Challenges
				GROUP BY hacker_id
				) sub
				GROUP BY challenges_created
				HAVING COUNT(*) = 1
)
ORDER BY challenges_created DESC,  h.hacker_id
;
SQL
복사

With statement

with로 만든 테이블은 임시테이블로 해당 쿼리에서 재사용이 가능하다

with counter AS (
	SELECT h.hacker_id
		, h.name
		, COUNT(*) challenges_created
	FROM Challenges c
		INNER JOIN Hackers h ON c.hacker_id = h.hacker_id
	GROUP BY h.hacker_id, h.name
)

SELECT counter.hacker_id
	, counter.name
	, counter.challenges_created
FROM counter
WHERE challenges_created = (SELECT MAX(challenges_created) FROM counter)
OR challenges_created IN 
(
SELECT challenges_created
FROM counter
GROUP BY challenges_created
HAVING COUNT(*) = 1)
ORDER BY counter.challenges_created DESC, counter.hacker_id	
SQL
복사