[MySQL] 꿀팁 몇가지

[WITH AS 로 CTE로 테이블 미리 빼놓기]

WITH ap AS (
    SELECT *
    FROM AIR_POLLUTION
    WHERE LOCATION1 = '경기도' AND LOCATION2 = '수원'
)

// ROUND 함수 -> 소수 셋째자리에서 반올림이면 두번째 자리 까지니까 2
SELECT YEAR(YM) AS YEAR, ROUND(AVG(PM_VAL1),2) AS 'PM10', ROUND(AVG(PM_VAL2),2) AS 'PM2.5'
FROM ap
GROUP BY YEAR(YM)
ORDER BY YEAR

WITH HE AS (
    SELECT DEPT_ID, ROUND(AVG(SAL),0) AS AVG_SAL
    FROM HR_EMPLOYEES
    GROUP BY DEPT_ID
)

SELECT HD.DEPT_ID, DEPT_NAME_EN, AVG_SAL
FROM HR_DEPARTMENT AS HD
JOIN HE ON HD.DEPT_ID = HE.DEPT_ID
ORDER BY AVG_SAL DESC

 

---

[ORDER BY 특정 필드 우선적으로 하기]

SELECT ANIMAL_TYPE, COUNT(*) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY FIELD(ANIMAL_TYPE, 'CAT', 'DOG')

 

---

[LIKE '%' 여러개 대신 REGEXP 사용하기]