New Companies (+ 형 변환)

www.hackerrank.com/challenges/the-company/problem

New Companies | HackerRank

해결 방법 : 다중 JOIN

• 모든 manager table을 company_code로만 연결해주면 된다.

SELECT c.company_code, c.founder, 
    count(distinct lm.lead_manager_code),
    count(distinct sm.senior_manager_code),
    count(distinct m.manager_code),
    count(distinct e.employee_code)
FROM company as c, lead_manager as lm, senior_manager as sm, manager as m, employee as e
WHERE c.company_code = lm.company_code
    and c.company_code = sm.company_code
    and c.company_code = m.company_code
    and c.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code;

 

ORDER BY문에서 당연히 company_code가 정수형으로 정렬되야 한다고 생각했는데, (ex. c1, c2, ... , c9, c10, c11)

요구하는 답은 문자열 형태로 정렬해야 한다..(ex. c1, c10, c11, c2, c21, ..) 

 

 

그래서 더 간단해지긴 했지만, 

만약 company_code를 정수형으로 정렬하라고 한다면 

문자열 앞에 'c'를 지우고 형 변환을 해줘야 할 것이다.

SELECT c.company_code, c.founder, 
    count(distinct lm.lead_manager_code),
    count(distinct sm.senior_manager_code),
    count(distinct m.manager_code),
    count(distinct e.employee_code)
FROM company as c, lead_manager as lm, senior_manager as sm, manager as m, employee as e
WHERE c.company_code = lm.company_code
    and c.company_code = sm.company_code
    and c.company_code = m.company_code
    and c.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY cast(substr(c.company_code, 2) as unsigned);

 

형 변환 방법들

• CONVERT(<원본>, <변경type>)
• CAST(<원본> as <변경type>)
• 묵시적 형 변환 : ex. '10' + 0, '10' * 1, etc

 

 

 

출처

docs.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15