프로그래머스에서 제공하는 SQL 고득점 kit를 기반으로 작성한 게시글입니다.
고득점 키트 보러가기
집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용
역순 정렬하기
SELECT NAME,DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC
조건 포함하고 검색
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = 'Sick' ORDER BY ANIMAL_ID ASC
제외하고 검색
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != 'Aged' ORDER BY ANIMAL_ID ASC
여러기준 정렬
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME ASC, DATETIME DESC
상위 n개 레코드
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1
최대값
SELECT MAX(DATETIME) FROM ANIMAL_INS
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME DESC LIMIT 1
최솟값
SELECT MIN(DATETIME) FROM ANIMAL_INS
SELECT DATETIME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1
COUNT
SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS;
중복제거
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS;
group by
SELECT ANIMAL_TYPE,COUNT(ANIMAL_TYPE) FROM ANIMAL_INS GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ASC
having
SELECT NAME, COUNT(NAME) AS COUNT FROM ANIMAL_INS GROUP BY NAME HAVING COUNT>1 ORDER BY NAME ASC
시각다루기 GROUP BY
SELECT HOUR(DATETIME) AS HOUR,COUNT(HOUR(DATETIME)) AS COUNT FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) HAVING HOUR >= 9 AND HOUR < 20 ORDER BY HOUR
없는 값도 출력하는 방법
SET @H := -1;
SELECT (@H := @H+1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @H) AS COUNT
FROM ANIMAL_OUTS
WHERE @H < 23
