.💌프로그래머스 / MYSQL / LEVEL 4 / 언어별 개발자 분류하기
- 프로그래머스 LEVEL 4 가장 어려운 문제, 정답률 낮은 문제임
- 특별한 CTE 쿼리를 요구하는 것은 아니나 조인을 하고 조건을 걸면서 생기는 제약사항이 까다로워 많은 사람들이 못풀었을 것으로 예상
- 며칠 걸려서 몇번 풀은 결과, 통과로 뜰 수 있었다! 얼마나 행복한지..!!!!
1.문제정의
2.STEP 별 풀었던 문제 풀이
- 비트 연산자 개념에 대해 알고 있어야함.(2진수)
- GROUP_CONCAT 함수를 통해 각 그룹별 값들을 나열하기
💡step1. full outer join , 이때 비트 연산자 활용 (&연산자)
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE💡step2. 컬럼 추가 생성, 각 조건별 Grades 출력할 수 있도록 조건 내 값들을 명시
SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T💡step 3 : 해당 조건 값이 없는 경우 삭제하는 구문 작성 (생략 가능)
SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T
) T2💡step 4. group_concat 활용, 각 그룹별 컬럼 값 나열
group_concat 유용, 특히 중복 제거 위해 group_concat(distinct 컬럼값) 작성 필요
SELECT ID,EMAIL,GROUP_CONCAT(DISTINCT GRADE) as GRADES
FROM
(
SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T
) T2
GROUP BY ID,EMAIL) T3💡step 5. grade a,b,c 구분
- Python 과 Front End 이 모두 들어있는 경우 ? A, C#이 있는 경우? B , 그 외 Front End 이 있는 경우 ? C
- 주의점 !!! in을 사용 xx
-> IN 은 특정값이 그대로 나와야 출력이 된다. & Front End,C#,Python인 경우에 IN ('Front End,Python')을 적으면 중간에 C#이 적혀있어서 count 되지 않으므로 앞뒤로 포함되어야지 출력할 수 있는 like 를 사용하기!
SELECT ID,EMAIL,
CASE WHEN GRADES LIKE '%Front End%' AND GRADES LIKE '%Python%' THEN 'A'
WHEN GRADES LIKE '%C#%' THEN 'B'
WHEN GRADES LIKE '%Front End%' THEN 'C'
ELSE NULL
END AS GRADE
FROM(SELECT ID,EMAIL,GROUP_CONCAT(DISTINCT GRADE) as GRADES
FROM
(
SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T
) T2
GROUP BY ID,EMAIL) T3
ORDER BY GRADE , ID) T4💡step 6. 최종 정답 ) grade null 값 삭제 (출력되면 안됨)
SELECT GRADE, ID, EMAIL
FROM
(SELECT ID,EMAIL,
CASE WHEN GRADES LIKE '%Front End%' AND GRADES LIKE '%Python%' THEN 'A'
WHEN GRADES LIKE '%C#%' THEN 'B'
WHEN GRADES LIKE '%Front End%' THEN 'C'
ELSE NULL
END AS GRADE
FROM(SELECT ID,EMAIL,GROUP_CONCAT(DISTINCT GRADE) as GRADES
FROM
(
SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T
) T2
GROUP BY ID,EMAIL) T3
ORDER BY GRADE , ID) T4
WHERE GRADE IS NOT NULL;
오답문항
SELECT
CASE
WHEN COUNT(DISTINCT CASE WHEN GRADE IN ('Python','Front End') THEN GRADE END) >= 2 THEN 'A'
WHEN COUNT(CASE WHEN GRADE = 'C#' THEN 1 END ) > 0 THEN 'B'
WHEN COUNT(CASE WHEN GRADE = 'Front End' THEN 1 END ) > 0 THEN 'C'
ELSE NULL
END AS GRADE,
ID,EMAIL
FROM
(SELECT ID,EMAIL,NAME,CATEGORY,
CASE
WHEN CATEGORY = 'Front End' THEN 'Front End'
WHEN NAME = 'Python' THEN 'Python'
WHEN NAME = 'C#' THEN 'C#'
ELSE NULL
END AS GRADE
FROM
(
SELECT *
FROM SKILLCODES A LEFT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
UNION
SELECT *
FROM SKILLCODES A RIGHT JOIN DEVELOPERS B
ON A.CODE & B.SKILL_CODE = A.CODE
) T
) T2
WHERE GRADE IS NOT NULL
GROUP BY ID,EMAIL
ORDER BY GRADE ASC, ID ASC;
