SELECT NAME, SALARY, DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RANK
FROM EMPLOYEES;
RANK()
함수 이용 : RANK() OVER (ORDER BY 정렬할 column명) AS 순위 매긴 column명
중복 순위 있을 경우,
RANK( ) : 건너 뜀 1 2 2 4 5
DENSE_RANK( ) : 건너 뛰지 않음 1 2 2 3 4
ORDER BY는 상황에 따라 DESC 붙여주면 됨
SELECT type, variety, price
FROM fruits
WHERE price = (SELECT MIN(price)
FROM fruits AS f
WHERE f.type = fruits.type);
MIN
함수로 최소값 구하고, 서브쿼리로 그거랑 같은 값 구하기코테 빈출
SELECT INS.ANIMAL_ID, INS.NAME, IFNULL(OUTS.SUB_COUNT, 0) AS COUNT
FROM ANIMAL_INS INS LEFT OUTER JOIN(SELECT ANIMAL_ID, NAME, COUNT(*) AS SUB_COUNT
FROM ANIMAL_OUTS
GROUP BY ANIMAL_ID) OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY INS. ANIMAL_ID
서브쿼리로 COUNT 구한 후, JOIN
IFNULL
함수로 NULL값 0으로 대체한 줄
-- SELECT
여러 줄
/* SELECT
FROM STUDENT
*/
SELECT (SELECT COUNT(*)
FROM COUPONS) AS APPLIED, COUNT(*) AS UNAPPLIED
FROM CARTS cart
WHERE cart.ID NOT IN (SELECT coupon.ID FROM CPUPONS coupon);
NOT IN
조건