-- 12세 이하인
-- 여자환자
-- 전화번호가 없는 경우, 'NONE'으로 출력
-- 나이를 기준으로 내림차순 정렬하고, 나이 같다면 환자이름을 기준으로 오름차순 정렬
SELECT PT_NAME, PT_NO, GEND_CD, AGE, TLNO
from PATIENT
where GEND_CD='W' and AGE<=12
order by AGE DESC, PT_NAME;
전화번호가 없는 경우, 'NONE'으로 출력
을 어떻게 작성해야할지 모르겠다.
찾아보니 IFNULL()
메서드가 있다는 것을 알게 되었다.
SELECT PT_NAME, PT_NO, GEND_CD, AGE, IFNULL(TLNO, "NONE") AS TLNO
from PATIENT
where GEND_CD='W' and AGE<=12
order by AGE DESC, PT_NAME;
✔️ IFNULL() 사용
SELECT WAREHOUSE_ID,WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N') as FREEZER_YN
from FOOD_WAREHOUSE
where ADDRESS like '경기도%'
order by WAREHOUSE_ID;
✔️ IF() 사용
SELECT WAREHOUSE_ID,WAREHOUSE_NAME, ADDRESS, IF(FREEZER_YN is null, 'N', FREEZER_YN) as FREEZER_YN
from FOOD_WAREHOUSE
where ADDRESS like '경기도%'
order by WAREHOUSE_ID;
✔️ CASE WHEN 사용
SELECT WAREHOUSE_ID,WAREHOUSE_NAME, ADDRESS,
CASE
WHEN FREEZER_YN is null THEN 'N'
ELSE FREEZER_YN
END AS FREEZER_YN
from FOOD_WAREHOUSE
where ADDRESS like '경기도%'
order by WAREHOUSE_ID;
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
/* 예시 1 */
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
/* 예시 2 */
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') as NAME, SEX_UPON_INTAKE
from ANIMAL_INS
/* count() 사용 */
SELECT count(*) as USERS
from USER_INFO
where AGE is null;
/* sum() 사용 */
SELECT sum(AGE is null) as USERS
from USER_INFO