[SQL문제다시풀기] 언어별 개발자 구하기 - GROUP_CONCAT

문제

MYSQL

내가 작성한 정답 1 - 서브쿼리

with a as (select d.id
            from developers d
            join skillcodes s on (d.skill_code & s.code) = s.code
            where s.category = 'Front End' 
                and id in (select d.id 
                           from developers d
                           join skillcodes s on (d.skill_code & s.code) = s.code
                           where s.name = 'Python')),
     b as (select d.id
            from developers d
            join skillcodes s on (d.skill_code & s.code) = s.code 
            where s.name = 'C#'),
     c as (select d.id
            from developers d
            join skillcodes s on (d.skill_code & s.code) = s.code 
            where s.category = 'Front End'),
    d as (select case
                    when id in (select * from a) then 'A'
                    when id in (select * from b) and id not in (select * from a) then 'B'
                    when id in (select * from c) and id not in (select * from a) 
                        and id not in (select * from b) then 'C' end grade,
                    id, email
            from developers
             order by 1 asc, id asc)
select * from d where grade is not null

내가 작성한 정답 2 - join

select * 
from (select case 
            when p.name is not null and f.name is not null then 'A'
            when c.name is not null then 'B'
            when f.name is not null then 'C'
            end grade
            , d.id, d.email 
    from developers d
    left join skillcodes p on (d.skill_code & p.code) = p.code and p.name = 'Python' 
    left join skillcodes c on (d.skill_code & c.code) = c.code and c.name = 'C#'
    left join skillcodes f on (d.skill_code & f.code) = f.code and f.category = 'Front End') a
where grade is not null
order by grade asc, id asc

내가 이전에 작성한 정답1 - intersect

WITH A AS ((SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.NAME = 'Python'
            GROUP BY D.ID, D.EMAIL)
             INTERSECT
            (SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.CATEGORY = 'Front End'
            GROUP BY D.ID, D.EMAIL)),
    B AS (SELECT D.ID, D.EMAIL
            FROM DEVELOPERS D
            JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0 
            WHERE S.NAME = 'C#' ),
    C AS (SELECT D.ID, D.EMAIL
         FROM DEVELOPERS D
         JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0
         WHERE (D.ID, D.EMAIL) NOT IN (SELECT * FROM A)
            AND (D.ID, D.EMAIL) NOT IN (SELECT * FROM B)
            AND S.CATEGORY = 'Front End'),
    D AS (SELECT CASE 
                    WHEN (ID, EMAIL) IN (SELECT * FROM A) THEN 'A'
                    WHEN (ID, EMAIL) IN (SELECT * FROM B) THEN 'B'
                    WHEN (ID, EMAIL) IN (SELECT * FROM C) THEN 'C'
                                        END AS GRADE, 
          ID, EMAIL
            FROM DEVELOPERS            
            )
SELECT * FROM D
WHERE GRADE IS NOT NULL
ORDER BY GRADE ASC, ID ASC

SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;

내가 이전에 작성한 정답 2 - join

WITH TB AS (
    SELECT 
        D.ID,
        D.EMAIL,
        CASE
            WHEN P.NAME IS NOT NULL AND F.NAME IS NOT NULL THEN 'A' 
            WHEN C.NAME IS NOT NULL THEN 'B' 
            WHEN F.NAME IS NOT NULL THEN 'C' 
        END AS GRADE
    FROM DEVELOPERS D
    LEFT JOIN SKILLCODES P ON P.NAME = 'Python' AND D.SKILL_CODE & P.CODE != 0
    LEFT JOIN SKILLCODES C ON C.NAME = 'C#' AND D.SKILL_CODE & C.CODE != 0
    LEFT JOIN SKILLCODES F ON F.CATEGORY = 'Front End' AND D.SKILL_CODE & F.CODE != 0
)
SELECT GRADE, ID, EMAIL
FROM TB
WHERE GRADE IS NOT NULL
// group by 필요없음 case when then에서 처음에 조건에 만족한 건 걸러지기 때문
GROUP BY GRADE, ID, EMAIL
ORDER BY GRADE ASC, ID ASC

다른 사람들이 작성한 정답

SELECT
        CASE 
        WHEN GROUP_CONCAT(B.NAME) LIKE ("%Python%") AND GROUP_CONCAT(B.CATEGORY) LIKE("%Front%") THEN "A"
        WHEN GROUP_CONCAT(B.NAME) LIKE ("%C#%") THEN "B"
        WHEN GROUP_CONCAT(B.CATEGORY) LIKE ("%Front%") THEN "C"
        END AS  GRADE ,
        A.ID,
        A.EMAIL

FROM DEVELOPERS  AS A
JOIN SKILLCODES AS B
ON (A.SKILL_CODE & B.CODE = B.CODE)
GROUP BY ID,EMAIL 
HAVING GRADE IS NOT NULL
ORDER BY GRADE ASC , ID ASC

-- 구분자 작성하지 않으면 기본값인 , 로 자동설정됨
SELECT GROUP_CONCAT(column_name SEPARATOR '구분자')
FROM table_name
GROUP BY column_name;