[SQL문제풀기] 언어별 개발자 분류하기

문제

MYSQL

내가 작성한 정답

WITH SKILLS AS (SELECT D.ID, GROUP_CONCAT(S.CATEGORY) CATEGORY, GROUP_CONCAT(S.NAME) NAME
                FROM DEVELOPERS D
                JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) = S.CODE
                GROUP BY ID),
     GRADES AS (SELECT CASE
                        WHEN CATEGORY LIKE '%Front End%' AND NAME LIKE '%Python%' THEN 'A'
                        WHEN NAME LIKE '%C#%' THEN 'B'
                        WHEN CATEGORY LIKE '%Front End%' THEN 'C'   END GRADE, ID
                FROM SKILLS)
SELECT G.GRADE, G.ID, D.EMAIL
FROM GRADES G
JOIN DEVELOPERS D ON G.ID = D.ID
WHERE GRADE IS NOT NULL
ORDER BY 1,2;

SELECT 
  그룹기준컬럼,
  GROUP_CONCAT(합칠컬럼 SEPARATOR '구분자') AS 별칭
FROM 테이블명
GROUP BY 그룹기준컬럼;
// 구분자 안쓰면 ,로 자동설정됨

GROUP_CONCAT(DISTINCT NAME ORDER BY NAME SEPARATOR ', ')

LISTAGG(NAME, '구분자') WITHIN GROUP (ORDER BY NAME)