[Programmers] SELECT(1)

3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, date_format(DATE_OF_BIRTH, '%Y-%m-%d') as DATE_OF_BIRTH 
from member_profile 
where month(date_of_birth) = 3 and TLNO is not null
and Gender ='W'
order by member_id asc;

 

재구매가 일어난 상품과 회원 리스트 구하기

SELECT USER_ID, PRODUCT_ID FROM
(SELECT USER_ID, PRODUCT_ID, COUNT(SALES_AMOUNT) AS CNT FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID) SUB
WHERE CNT>1
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

SELECT USER_ID, PRODUCT_ID, COUNT(1) AS CNT, COUNT(SALES_AMOUNT)
FROM ONLINE_SALE
GROUP BY 1,2
HAVING COUNT(1)>=2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

 

업그레이드 된 아이템 구하기

SELECT ITEM_ID,ITEM_NAME,RARITY FROM ITEM_INFO 
WHERE ITEM_ID IN(
SELECT IT.ITEM_ID 
    FROM ITEM_INFO II 
    LEFT JOIN ITEM_TREE IT 
    ON II.ITEM_ID = IT.PARENT_ITEM_ID 
    WHERE II.RARITY='RARE' 
    AND IT.PARENT_ITEM_ID IS NOT NULL)
ORDER BY ITEM_ID DESC;

SELECT C.ITEM_ID, C.ITEM_NAME, C.RARITY
FROM ITEM_INFO P, ITEM_TREE IT, ITEM_INFO C
WHERE P.RARITY = 'RARE'
AND P.ITEM_ID = IT.PARENT_ITEM_ID
AND C.ITEM_ID = IT.ITEM_ID
ORDER BY C.ITEM_ID DESC;