[sql]프로그래머스 sql문제정리1(select, 집계함수, group by)

 

select

-역순 정렬하기

https://programmers.co.kr/learn/courses/30/lessons/59035

SELECT name, datetime from ANIMAL_INS order by animal_id desc

 

- 여러기준으로 정렬하기

SELECT animal_id, name, datetime from ANIMAL_INS order by name asc, datetime desc

 

- 상위 n개 레코드

https://programmers.co.kr/learn/courses/30/lessons/59405

SELECT name from ANIMAL_INS order by DATETIME asc limit 1

 

SUM, MAX, MIN

 

-최댓값 구하기

가장 늦게 들어온 동물

https://programmers.co.kr/learn/courses/30/lessons/59415#qna

# SELECT DATETIME as 시간 from ANIMAL_INS order by datetime desc limit 1

SELECT max(datetime) as '시간' from animal_ins;

 

-최솟값

가장 빨리 들어온 동물

https://programmers.co.kr/learn/courses/30/lessons/59038

SELECT MIN(datetime) as '시간' from animal_ins;

 

- 세기

count함수, 중복제거

https://programmers.co.kr/learn/courses/30/lessons/59406

SELECT count(distinct animal_id) from ANIMAL_INS

 

GROUP BY

 

-개수 세기

https://programmers.co.kr/learn/courses/30/lessons/59040

SELECT animal_type, count(animal_type) 
from ANIMAL_INS 
group by animal_type order by animal_type asc

 

-having

https://programmers.co.kr/learn/courses/30/lessons/59041#qna

SELECT name, count(name)
from ANIMAL_INS 
group by name
having count(name) >1
order by name asc

 

-having, 시간 쪼개기

https://programmers.co.kr/learn/courses/30/lessons/59412

SELECT hour(AO.datetime) h , count(hour(AO.datetime))
from ANIMAL_OUTS AO
group by h
having h >=9 and h<20
order by h asc