HackerRank에서 문제를 풀면서 짤막하게 정리한 내용
몇년 전에도 SQL을 공부했었지만 역시나 안써서 그런지 잊어버린다
블로그 주인장이 볼 내용이라 깔끔한 정리는 안되어 있음!!
- distinct -> 중복값 제거
ex) select distinct col from table_name;
- 각 값의 차이 구하기
ex) select count(city) - count(distinct city) from station;
- order by 사용, top 사용하여 첫번째 컬럼 사용하기, 길이 구하기
ex) select top 1 city as city_name, len(city) as len from station order by len(city), city;
select top 1 city as city_name, len(city) as len from station order by len(city) desc, city;
- like 문자 비교
ex) select city from station where city like '[aeiouAEIOU]%' group by city;
- 문자열 자르기
right(문자열, 길이) -> left도 마찬가지
조건문 뿐만 아니라 어디에도 사용 ok
ex) select name from students where marks > 75 order by right(name, 3), id asc;
- 테이블 교집합
inner join
ex) select country.continent, floor(avg(city.population)) from city inner join country on city.countrycode = country.code group by country.continent;
- 조건문 case when ~ then ~ else ~ end
ex) select
(case when grades.grade < 8 then null
else students.name
end) as name,
grades.grade as grade,
students.marks as marks
from students join grades
on students.marks >= grades.min_mark
and students.marks <= grades.max_mark
order by grades.grade desc, students.name;
- group by ~ having ~
테이블을 그룹으로 묶는데 값은 반드시 통합된(공통된) 값이나
집계합수(sum 등등)를 사용한 하나의 값만을 출력해야함
- substring 구문 내 구문
ex) select w.id, p.age, w.coins_needed, w.power
from wands w join wands_property p
on w.code = p.code
where p.is_evil = 0
and w.coins_neeeded =
(select min(w1.coins_needed)
from wands w1 join wands_property p1
on w1.code = p1.code
where w1.power = w.power and p1.age = p.age)
order by w.power desc, p.age desc;
- 임시 테이블 생성
with 테이블 이름
as ( select ~ )
ex) /* 각 user의 총 도전 과제의 갯수 */
with data
as
(
select c.hacker_id as id, h.name as name, count(c.hacker_id) as counter
from hackers h
join challenges c on c.hacker_id = h.hacker_id
group by c.hacker_id, h.name
)
/* 위의 구문으로부터 select함 */
select id, name, counter
from data
where counter = (select max(counter) from data)
/* 최대 미션 갯수를 가진 user를 select함 */
or
counter in
(select counter from data group by counter having count(counter) = 1)
/* 단일한 미션 갯수를 나타내도록 필터 */
order by counter desc, id;